Interpretation of ‘;’ and ‘/’ by Oracle’s SQL command line utility

SQLPLUS* command line utility is a tool that we use quite often to execute the Oracle’s sql commands that we wish to execute quickly in the database.
Sometimes there are scenario’s where we use this utility to execute serious of sql commands (sql script) from a console as part of deployment etc., as shown below

c:>call sqlplus scott/tiger@localhost @EXECUTE_MY_SCRIPT.sql

When executing the sql commands from a sql script it becomes highly necessary to understand how the sqlplus command line utility interprets the ‘ ; ‘ and ‘ / ‘ which are marked basically marked as end of statement. Sometimes ignoring this subtle difference could cause errors being thrown from the sqlplus utility.

Generally including ‘ / ‘ after a sql statement means that we are telling sqlplus utility that the previous sql statement has ended and asking it to execute the previous sql statement one more time.

The following screen shot will help you understand that easily:
sqlplus1

In the above execution you can notice that appending ‘ ; ‘ at the end of sql statement, made the sqlplus utility to execute the statement. And introducing ‘ / ‘ in the prompt immediately next to the statement execution, makes the previous statement to executed one more time.

Here is another version of using both ‘ ; ‘ and ‘ /

sqlplus2

In the above screen shot we can notice that even if we are not providing a ‘ ; ‘ at the end of the statement, including a ‘ / ‘ in the next line is making the sqlplus utility to execute the sql statement.

Thus it is how sqlplus command line utility interprets ‘ ; ‘ and ‘ / ‘.

But, Why is it important to understand this subtle difference?

Checkout this below script to see what happens if ‘ ; ‘ and ‘ / ‘ are used in a wrong way on a sql script which creates a table something as follows:

Assuming that you’ve got an sql script, EXECUTE_MY_SCRIPT.sql which has got sql statements as follows:

sqlplus3

Try executing the above script from a console using the command as follows:

c:>call sqlplus scott/tiger@localhost @EXECUTE_MY_SCRIPT.sql

The output will be as follows:
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Apr 17 06:29:52 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

Table created.

1 row created.

INSERT INTO TEMP (COL_1, COL_2) VALUES (1, ‘Y’)
*
ERROR at line 1:
ORA-00001: unique constraint (ORCLDB.SYS_C0032771) violated

Hope this blog post was useful. 🙂

Advertisements

About this entry