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

I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.

There is a huge difference in SQL*Plus between the meaning of a / and a ; because they work differently.

The ; ends a SQL statement, whereas the / executes whatever is in the current "buffer". So when you use a ; and a / the statement is actually executed twice.

You can easily see that using a / after running a statement:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning and OLAP optionsSQL> drop table foo;Table dropped.SQL> /drop table foo           *ERROR at line 1:ORA-00942: table or view does not exist

In this case one actually notices the error.


But assuming there is a SQL script like this:

drop table foo;/

And this is 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 2012Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning and OLAP optionsSQL> @dropTable dropped.drop table foo           *ERROR at line 1:ORA-00942: table or view does not exist

The / is mainly required in order to run statements that have embedded ; like CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE statements and for any BEGIN...END blocks.

I wanted to clarify some more use between the ; and the /

In SQLPLUS:

1. ; means "terminate the current statement, execute it and store it to the SQLPLUS buffer"
2. <newline> after a D.M.L. (SELECT, UPDATE, INSERT,...) statement or some types of D.D.L (Creating Tables and Views) statements (that contain no ;), it means, store the statement to the buffer but do not run it.
3. / after entering a statement into the buffer (with a blank <newline>) means "run the D.M.L. or D.D.L. or PL/SQL in the buffer.
4. RUN or R is a sqlsplus command to show/output the SQL in the buffer and run it. It will not terminate a SQL Statement.
5. / during the entering of a D.M.L. or D.D.L. or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer"

NOTE: Because ; are used for PL/SQL to end a statement ; cannot be used by SQLPLUS to mean "terminate the current statement, execute it and store it to the SQLPLUS buffer" because we want the whole PL/SQL block to be completely in the buffer, then execute it. PL/SQL blocks must end with:

END;/

It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing a DML statement) can be picked out more easily by eye.

Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.