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
create or replace trigger foo_trig_bi
before insert on foo
for each row
:new.id := nvl(:new.id,foo_seq.nextval) ;
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.
- 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:
- Simple SQL statements can be terminated by a semicolon OR a slash.
- 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.
Once we all understand the situation and code accordingly, everyone is happy.
Get our latest content delivered to your inbox and stay informed on our upcoming educational events.
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