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

Question

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 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> 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 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> @drop

Table 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.

Almost all Oracle deployments are done through SQL*Plus (that weird little command line tool that your DBA uses). And in SQL*Plus a lone slash basically means "re-execute last SQL or PL/SQL command that I just executed".

See

http://ss64.com/ora/syntax-sqlplus.html

Rule of thumb would be to use slash with things that do BEGIN .. END or where you can use CREATE OR REPLACE.

For inserts that need to be unique use

INSERT INTO my_table ()
SELECT <values to be inserted>
FROM dual
WHERE NOT EXISTS (SELECT 
                  FROM my_table
                  WHERE <identify data that you are trying to insert>)

From my understanding, all the SQL statement don't need forward slash as they will run automatically at the end of semicolons, including DDL, DML, DCL and TCL statements.

For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.