SQL: the semicolon or the slash?

Question

We have been having some debate this week at my company as to how we should write our SQL scripts.

Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production.

Now, we had a deploy recently that failed because it had used both a semicolon and a forward slash (/). The semicolon was at the end of each statement and the slash was between statements.

alter table foo.bar drop constraint bar1;
/
alter table foo.can drop constraint can1;
/

There were some triggers being added later on in the script, some views created as well as some stored procedures. Having both the ';' and the '/' caused each statement to run twice causing errors (especially on the inserts, which needed to be unique).

In SQL Developer this does not happen, in TOAD this does not happen. If you run certain commands they will not work without the '/' in them.

In PL/SQL if you have a subprogram (DECLARE, BEGIN, END) the semicolon used will be considered as part of the subprogram, so you have to use the slash.

So my question is this: If your database is Oracle, what is the proper way to write your SQL script? Since you know that your DB is Oracle should you always use the /?

(Please be gentile, I am far from being a Database Guru, but trying to learn. I didn't see any questions here that answered my question.)

Answer 1

I only use the forward slash once at the end of each script, to tell sqlplus that there is not more lines of code. In the middle of a script, I do not use a slash.

Answer 2

It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing a DML statement) can be picked out more easily by eye.

Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.

Answer 3

Almost all Oracle deployments are done through SQL*Plus (that weird little command line tool that your DBA uses). And in SQL*Plus a lone slash basically means "re-execute last SQL or PL/SQL command that I just executed".

See http://ss64.com/ora/syntax-sqlplus.html

Rule of thumb would be to use slash with things that do "BEGIN .. END" or where you can use "CREATE OR REPLACE".

For inserts that need to be unique use

INSERT INTO my_table ()
SELECT <values to be inserted>
FROM dual
WHERE NOT EXISTS (SELECT 
                  FROM my_table
                  WHERE <identify data that you are trying to insert>)