When do I need to use a semicolon vs a slash in Oracle SQL?

Question

We have been having some debate this week at my company as to how we should write our SQL scripts.

Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production.

Now, we had a deploy recently that failed because it had used both a semicolon and a forward slash (/). The semicolon was at the end of each statement and the slash was between statements.

alter table foo.bar drop constraint bar1;

/

alter table foo.can drop constraint can1;

/

There were some triggers being added later on in the script, some views created as well as some stored procedures. Having both the; and the / caused each statement to run twice causing errors (especially on the inserts, which needed to be unique).

In SQL Developer this does not happen, in TOAD this does not happen. If you run certain commands they will not work without the / in them.

In PL/SQL if you have a subprogram (DECLARE, BEGIN, END) the semicolon used will be considered as part of the subprogram, so you have to use the slash.

So my question is this: If your database is Oracle, what is the proper way to write your SQL script? Since you know that your DB is Oracle should you always use the /?

Answer 1

There is a difference in SQL*Plus between the meaning of /and; because they perform in a different manner.

The ; is used to end the SQL statement, Whereas the / is used to execute whatever is in the current "buffer". Therefore, when you use; and / the statement gets executed twice.

You can observe using a / after running a statement:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012

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

Which are connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning and OLAP options

SQL> drop table foo;

The above code is used for dropping the Table.

SQL> /

drop table foo

           *

ERROR at line 1:

ORA-00942: Here, table or view does not exist

In the above case, one can easily notice the error. 

But assume that there is a SQL script like this:

drop table foo;

/

Here, / is the run from within SQL*Plus then this will be very confusing:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012

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

It is connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning and the OLAP options like this

SQL> @drop

The table is dropped.

drop table foo

           *

ERROR at line 1:

ORA-00942: table or view does not exist

Here, the / is mainly required in order to run statements that have embedded; like a CREATE PROCEDURE statement.