Click here to Skip to main content
15,867,568 members
Articles / Oracle

Oracle: DDL and Forward Slash

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
26 Dec 2013CPOL3 min read 7.9K  
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 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.,

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

and:

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

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
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:

SQL
SELECT <function name> FROM dual;

OR EXEC <procedure name>

Filed under: CodeProject, Databases, Oracle, Scripting
Tagged: Oracle, PL/SQL, Run command, Slash (punctuation), SQL

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) City of Los Angeles
United States United States
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!

Comments and Discussions

 
-- There are no messages in this forum --