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. Hence the need to better understand slashes and semicolons in oracle ddl scripts.

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.

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.

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.

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.

Conclusion on Slashes and Semicolons in Oracle ddl Scripts

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

Learn more about SQL Developer and Oracle APEX: Contact Us!