Tips on using Oracle SQLPLUS at UWE

Chris Wallace Jan 2003

Starting SQLPLUS

From the command prompt on UNIX:

      SQLPLUS username/password

You can execute a file of commands as follows:

     
      SQLPLUS username/password < filename

This may be more convenient than entering commands with SQLPLUS because its easier to re-execute a set of commands, thanks to the Unix history.

From Windows, use the SQLPLUS tool to log in (host string is CEMS901). I'm not sure where scripts are read from, but you can cut and paste commands into the SQLPlus window.

Using SQLPLUS

Enter commands at the prompt. Commands in SQLPLUS are case-insensitive

exit : to exit or quit SQLPLUS, type
```
     exit
     
```
to pause the output, so that output does not scoll off the screen
```
     set pagesize 20
     set pause on
```
Pressing 'return' goes on to the next screen full
help : help on SQLPLUS , (but not SQL) is available: enter:
```
     help index
     
```
for the index of commands
describe: To see the current definition of a table or type:
```
      describe name
e.g.  describe latlong
```
editing: The line editor in SQLPLUS is very basic. You can recall a line in the previous command by typing its number. After that line is re-displayed, you can edit it with
```
     C/oldstring/newstring
```
To re-execute the previous command:
```
     /
     
```
SQL commands: Commands may be continued across multiple lines. Terminate SQL commands with ; e.g.
```
     select * from boats;
     
     
```

Using scripts

Since line editing is minimal, it is much easier to use an editor to create/modify a command or sequence of commands and then load these into SQLPLUS. This works even for a single select statement as well as a complex script defining several tables, a number of INSERT statements to load data into a table or even a query

Create the commands in any editor. Save the file with a .SQL suffix.

In SQLPLUS, load the file, from the current directory on Unix, with

     GET filename

And execute with

or, more commonly,load and and execute with

     @filename

Remember that this will look for files in the current directory. IF SQLPLUS cannot find the file, you may have started SQLPLUS from a different directory.

Creating tables

In a script defining a number of tables, start the script with

     DROP TABLE tabname;

statements, in an appropriate order so that foreign key integrity constraints are not violated (i.e. child before parent).

Access /Oracle SQL differences

There are a few differences between the two versions of SQL which should be noted:

Outer join.
Whereas Access allows the notation
```
     SELECT * FROM  a LEFT JOIN b ON a.id = b.id;
```
The equivalent in Oracle would be
```
     SELECT * FROM a, b WHERE a.id=b.id(+)
```
The + indicates the side of the join which may contain null values (because of unmatched rows in the other table)
Group functions(sum(), avg() etc ignore NULLS in Oracle.
Use COUNT(1) in place of COUNT(*) for better efficiency
Substituting for NULLS
```
     NVL(colname,’nullvalue’)&#;
```
A column function. Null values in the designated column will be replaced with the designated value e.g.
```
     NVL(comm,’s'salary only'’)<
```

Expression evaluation

     SELECT exp1, exp2.. FROM DUAL;

Allows output of arbitrary expression e.g.

     SELECT sqrt(6) FROM DUAL;

Concatenate strings with
```
      ||
```

Comments

     -- This is a one line comment terminated by the end of the line.

Use them to document scripts.

To prompt for user input at Runtime, i.e. when a query is executed, prefix a string with & e.g.
```
    SELECT * FROM emp WHERE ename = &name;
```