Running SQL on Oracle



Running SQL on Oracle.

There are currently two ways to access Oracle during the tutorials.

1) sqlplus

2) activeSQL

sqlplus is the standard Oracle-supplied SQL interface for developers. It presents the user with a telnet-like interface, into which the user can type SQL directly and watch it execute. To use sqlplus, you need an Oracle username and password from C+IT services, plus a host string identifier. In the past this identifier was “SOC1.UK.DCS.NAPIER.AC”, but it may have changed by the time it is available in the JKCC (this document was written before that). For this year this interface should be considered the high-reliability method for tutorials and coursework.

This year I have written a web-based application to try and help students with the tutorials, and to provide on-line marking of the coursework. This interface is known as activeSQL. It can be found at



As it has only just been written, it may prove to be unreliable. However, it offers a number of advantages over sqlplus:

• It allows you to edit sql quite easily

• It remembers sql which you typed in last time you tackled a particular question

• It can be used from anywhere in the internet, not just from the JKCC.

• It attempts to analyse your answer to a question, and provide a little bit of feedback.

• It allows people to submit the coursework online and thus get your results back faster.

The feedback the system provides is not as good as a lecturer looking at your SQL and helping you directly! However, it is significantly better than that provided by sqlplus, and it will continue to be improved in the future.

To use activeSQL, you MUST register using your napier email address as the username. If you do not do this you will not be able to submit the coursework assessment online.

I recommend activeSQL over sqlplus, and provided that activeSQL is working for you I suggest you keep using it. However, you will not lose marks for using sqlplus for your tutorial work.

Should at any time a problem arise with activeSQL, please revert back to the sqlplus interface. Don’t send me email about it, as 300 students all telling me oracle is down is just not useful. I am happy to hear about problems at the tutorials or in person. I promise to try and get it working quickly.

SQL Plus

Often to get running in sqlplus you need to first run 1 or more scripts. When you first get an sqlplus account, the database you see is completely empty. Running these scripts creates the databases for your tutorials and coursework. You only have to run the scripts once for each username. DO NOT RUN THEM EACH TIME YOU LOG IN! The scripts can be found from my home page or from a tutorial tutor.

The data you create can only be seen by yourself, and there are no safeguards to prevent you from deleting your own tables. If this happens, rerun the scripts and the data will be generated. To confirm the tables have been created, run “select * from cat” and you should see a list of 5 tablenames. Running “select * from employee” should return about 32 rows of data. If this doesn’t happen rerun the scripts as required.

This oracle system is looked after by C+IT services and NOT me. It does not have a backup and recovery policy.

ActiveSQL

In activeSQL, all the data you need for tutorials and coursework are loaded into the database already. Do not try to run the scripts in activeSQL yourself, as you will not be allowed to by the interface. Safeguards prevent you from damaging the tables. Your commands are limited to “create view”, ”drop view”, “select”, and “describe”. This is more than sufficient to complete the tutorials and coursework.

Please be patient when running activeSQL. If there is a problem with your query wait for the web page to tell you the problem. Hitting “reload” 10 times only slows oracle down, and does not get you the result any faster. It will almost always be the case that a slow query execution time is the result of an error in your SQL which is producing significantly more data than you desired. Take this time to look at your query for potential causes.

Do not try and hack the system. It has reasonable security but contains nothing of value. More than likely a hacking attempt will simply shut the database down.

Backups are taken once per day during the working week. Thus if there is a problem you may lose work done over the last 24-48 hours. Bear this in mind when working on your coursework. The oracle system this runs on is looked after by me and NOT C+IT services.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download