Oracle: DDL and Forward Slash

In this blog,  I write about various technologies I come across. Only a few topics seem to get a lot of attention. One of them happens to be Forward slash in (Oracle) SQL. I've already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding of it's usage. A search through the web quickly reveals that forward slash has been a source of confusion even among some of the experienced Oracle SQL developers ¹. Based on the search terms, I can see the newcomers are often getting confused by the SQLs left for them by their predecessors and are looking for a clear explanation. "/" is often mixed in various statements, such as DDL, COMMIT etc and this leads to confusion about the statement itself. I will try to explain the role of "/" (or lack thereof) in each different scenario in separate posts.

One of the search term that I get a lot is "DDL and Forward slash" or some variation of it. Forward slash has nothing to do with DDL per se. If you saw my other posts, it is just a statement executor (RUN command for both SQL and PL/SQL). Unfortunately, semi-colon doubles as statement terminator and executor for SQLs (poor choice of shortcut) in SQL*Plus. There lies the confusion. Thus, the CREATE TABLE or DROP VIEW statements can be terminated by a semi-colon as well as "/".

For e.g.,

CREATE TABLE employee(
    emp_id NUMBER,
    emp_name VARCHAR2(40));

and

CREATE TABLE employee(
    emp_id NUMBER,
    emp_name VARCHAR2(40))
/

are identical as for SQL*Plus is concerned. But, as a matter of preference, people tend to use "/" in DDL.

But this is not the case in other DDL statements that involve PL/SQL:

CREATE OR REPLACE FUNCTION f_get_employee(a_emp_id NUMBER)
RETURN VARCHAR2
AS
    v_emp_name VARCHAR2(40);
BEGIN
   SELECT emp_name
   INTO v_emp_name
   FROM employee
   WHERE emp_id = a_emp_id;
END;
/

Here "/" is a must, as the above statement is one PL/SQL statement (from CREATE to END;). Remember, PL/SQL block ends with END; and it has to be followed by "/" to be executed ^{2 (see notes below)}.  Thus some DDL statements (those that create programming constructs like packages, procedures, triggers) require "/" at the end. This, I think, led the newcomers to believe there is a link between "/" and DDL!!! There is no connection! You cannot generalize the use of "/" for all DDLs. For e.g., if you tried to do this (semi-colon followed by "/") in a CREATE TABLE statement, you will have unexpected behavior:

SQL> CREATE TABLE employee(
2 emp_id NUMBER,
3 emp_name VARCHAR2(40));
Table created.SQL> /
CREATE TABLE employee(
*
ERROR at line 1:
ORA-00955: name is already used by an existing objectSQL>

In this case, the semi-colon followed by slash is a bad idea.

In a nutshell, use only semi-colon or slash at the end of a SQL (DML or DDL) and semi-colon followed by slash at the end of a PL/SQL. See here for the difference between SQL and PL/SQL).

As a personal choice, I normally use "/" for all DDLs (SQL or PL/SQL) and semi-colon for all DMLs. You can have one DDL per file in which case, the "/" is the last character in the file. You can also have multiple DDLs in a file, each separated by "/".  Try not to mix DDLs and DMLs in the same file! This may lead to more confusion and errors!

References

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

http://docs.oracle.com/html/A90108_01/sqcmd.htm

Notes:

---

1. See here for e.g., https://forums.oracle.com/thread/1020117

2. When I say "executed", I mean the PL/SQL block that creates the function/procedure is executed - meaning the program is compiled and stored in the database. (Hence the name stored programs.) The actual program execution is done by

SELECT <function name> FROM dual;

OR EXEC <procedure name>