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.

Subscribe

Get our latest content delivered to your inbox and stay informed on our upcoming educational events.

Related Content

Power Ranking Oracle APEX 18.1 New Features – May

FINALLY! After months of anticipation, Oracle Application Express (APEX) version 18.1 has arrived! Taking a look at the new features included definitely makes it...

Learn More
Dynamic Actions in Oracle Application Express – Go From Basic to Awesome

If you’re just starting out in Oracle Application Express, know that Dynamic Actions can take your applications from basic to awesome very quickly. And,...

Learn More
Collaborative Development in Oracle APEX – Steering Clear of Trouble

The ability to work collaboratively as a team is certainly one of the wonderful aspects of Application Express, Oracle's rapid application development platform. Developers work...

Learn More
Progressive Web Apps: Two worlds colliding to everyone’s benefit

The Story of Progressive Web Apps (PWAs) is now playing out at the frontier of two worlds, inside your smartphone. Once upon a time, inside...

Learn More
Blockchain – Technology you’ll have to Address, Sooner or Later

Blockchain is a technology buzzword getting a lot of attention these days. At its core, this technology is a means for securing data in...

Learn More

Related Case Studies

Ivy League University

A large department at an Ivy-league University needed to quickly streamline its mainly manual salary increase process.

Learn More
CIS Field Reporting System with Oracle APEX

Using Oracle APEX, Insum helped CIS develop a field data capture system for use in large scale fiber optics installation projects. The system is...

Learn More
Tap 50:50 Raffle System with Oracle APEX

Using Oracle APEX, Insum developed a shopping cart that could securely dovetail with Tap 5050’s existing system and provide a quality user experience.

Learn More

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 *