SQL*PLUS:

Entering SQL*Plus and SQL Commands

During an SQL*Plus session, both SQL*Plus and SQL commands can be entered at the SQL> prompt. Enter the commands on the line in free format.

SQL commands are terminated with a semi-colon (;). If a command is extended over several lines, press the enter key after each line and enter a semi-colon at the end of the last line. The command will not be executed until a semi-colon is encountered.
 

Exiting SQL*Plus

To exit from SQL*Plus use either EXIT or QUIT.
 

Buffer Manipulation

Oracle stores the most recently entered command in a buffer. This command can be changed, saved or re-run.

To change the current line:

change /old-string/new-string/ or c /old-string/new-string/
This command will change the first occurrence of old-string to new-string in the current line.

To execute the changed command, use the RUN command.

For example, suppose the following command has been entered:

SQL> select portfoliono, csh
2 from portfolio; This will generate the following error message: select portfoliono, csh
*

ERROR at line 1:
ORA-00904: invalid column name

 The following commands can be used to change the error and re-run the command: SQL> c /csh/cash/
1* select portfoliono, cash
SQL> run
1 select portfoliono, cash
2* from portfolio
PORT CASH
---- ---------
P001 25000
P004 10000
P005 50000
P008 37500
P011 8000 In the command displayed by SQL*Plus, the current line is shown with an *. To make a particular line the current line, enter the line number at the prompt.

In the following example, line 1 is made the current line and then this command line is altered:

SQL> 1
1* select portfoliono, cash
SQL> c /,/,managerno, /
1* select portfoliono,managerno, cash
SQL> run
1 select portfoliono,managerno, cash
2* from portfolio
PORT MA CASH
---- -- ---------
P001 M2 25000
P004 M3 10000
P005 M2 50000
P008 M3 37500
P011 M3 8000 To save the current command, enter the command:
save filename


Running Script Files

Script files can be executed by using the @ or START command:

@filename or start filename
The complete path for the file must be specified. If an extension is not included in the file specification, it is assumed to be SQL.

Example:

If the command:

start a:\load-data
is entered, the A: drive will be searched for a file called: load-data.sql.

If you wish to pass parameters to the script file, list them after the filename, in order, separated by spaces.
 

Spooling Output to a File

Output from a session can be logged to a file using the SPOOL command. To start spooling, enter the command:

spool filename
where filename is the name of the file to contain the output.

To turn the spooling off, enter the command:

spool off
If you are executing a script file and wish to capture the commands from that file, use the SET ECHO command:
set echo {on|off}


Listing Data Dictionary Information

The DESCRIBE command lists the column definitions for a given table - the column name, data type and whether the column allows nulls or not.

Example: To list the information for the PORTFOLIO table:

describe portfolio