09Mar
By: Patrick Cimolini On: March 09, 2017 In: Oracle APEX Comments: 3

Understanding Slashes and Semicolons in Oracle DDL Scripts

Data Definition Language Problems

Oracle developers are often asked to create Data Definition Language (DDL) scripts to support an application. These scripts create database objects like tables, views, triggers, and PL/SQL packages.

The developer uses a favorite database GUI tool (ex. Oracle’s SQL Developer) to create and test the scripts. Rigorous tests show that everything works.

The developer then sends the script to a Database Analyst (DBA) so that the scripts can be run into a production database. The DBA does not do this; instead, the script is immediately sent back to the developer to get fixed. The developer is confused because explicit tests have proven that the script works flawlessly.

So what’s up with this?

Cause of the Issue

Here is the simplified workflow that leads to the problem.

1. The developer creates a table with a trigger by using the following SQL code.

drop table foo;

/

create table foo

( id number

,code varchar2(10)

,description varchar2(100)

) ;

/

create or replace trigger foo_trig_bi

before insert on foo

for each row

begin

:new.id := nvl(:new.id,foo_seq.nextval) ;

end ;

/

2. The developer runs this code repeatedly in SQL Developer to make sure it works flawlessly. SQL Developer’s output indicates that all is good.

 

sql developer

 

3. The developer then sends the “flawless” script to the DBA who runs the “flawless” script using SQL*Plus. The result is not “flawless”. The code tries to drop the table twice and create the table twice. Errors are not good when the script will be run in a production database.

sql plus production database

 

Mastering Your Slashes and Semicolons

The solution is simple. Do one of the following in the script file:

  • Remove the slashes that come after the drop and the table create Leave the semicolons in place.

OR

  • Remove the semicolons that come after the drop and the table create Leave the slashes in place.

The solution works because SQL*Plus runs a simple SQL statement when it sees a trailing semicolon. It also runs the code that it finds in its buffer when it sees the trailing slash. Thus, the simple statements are run twice.

The trigger, which is a block of PL/SQL code, needs both the semicolon and the slash at the end of the block. In this case, the semicolon is actually part of the PL/SQL syntax and is stored with the PL/SQL in the buffer; the last semicolon in the PL/SQL block is not part of the SQL statement that runs the code. The slash runs the code in the buffer.

The rules for SQL*Plus are summarized as:

  1. Simple SQL statements can be terminated by a semicolon OR a slash.
  2. PL/SQL blocks must be terminated by a slash.

The rules are illustrated in the following SQL*Plus snippets.

slashes sql statements   semicolons sql lpsql

SQL Developer is a bit friendlier because it automatically does the correct action no matter how the SQL statements are terminated.

 

Comments on the Tools

SQL Developer and similar products are rich GUI tools that developers love; however, in the past, the early versions were buggy. The buggy history makes DBAs uneasy with these tools.

SQL*Plus, by comparison, has a very “old school” command line interface; however, it has a long history and many DBAs consider it to be the “gold standard” of stability and reliability.

As a result, many DBAs run scripts into their production databases with SQL*Plus, a tool that they trust. This conservative point of view reflects the fact that when a production database goes down, the DBA is hanged, not the developer.

Once we all understand the situation and code accordingly, everyone is happy.

3 Comments:

    • Kristine
    • March 09, 2017
    • Reply

    How about everyone get on the same page when it comes to dev IDE? Is that too much to ask?
    Aside: SQL Developer has so many memory leaks, our shop invested in TOAD. Because it just works.

      • Patrick Cimolini
      • March 13, 2017
      • Reply

      Hi Kristine,
      When it comes to computer tools, every developer has strong feelings about their personal tool set. The choice of tool depends on personal preferences and the nature of the work.
      Choosing between SQL Developer versus TOAD falls into this realm. Both products work well with SQL Developer leading in the cost area (it is free).
      Cheers,
      Patrick Cimolini

    • Mario
    • March 17, 2017
    • Reply

    Thanks Patrick. Very useful info. It just helped me to understand an issue I had with a script. Thanks.

Leave reply:

Your email address will not be published. Required fields are marked *