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
1. 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!
Referenceshttp://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commandshttp://docs.oracle.com/html/A90108_01/sqcmd.htmNotes:
1. See here for e.g., https://forums.oracle.com/thread/10201172. 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 bySELECT <function name> FROM dual;
OR EXEC <procedure name>
