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 its 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 that 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 terms 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 far as 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
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>
Filed under: CodeProject, Databases, Oracle, Scripting
Tagged: Oracle, PL/SQL, Run command, Slash (punctuation), SQL
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!