225

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 /?

Roland
  • 7,525
  • 13
  • 61
  • 124
amischiefr
  • 4,750
  • 2
  • 29
  • 22
  • 1
    In case someone is doing a database export with SQLDeveloper there is a checkbox called "Terminator" which when selected uses semicolons to terminate each statement. This option is selected by default. Un-select to remove semicolons and to avoid duplicate statement execution – Ruslans Uralovs Jan 04 '12 at 10:51
  • 11
    Just to pointlessly flog this old thread to death, I'll mention that the SQL language has no semicolon. It is merely the default terminator character in SQL*Plus (you can set `sqlterminator` to `!` if you like) and this convention tends to be followed by other tools. The PL/SQL language however does use semicolons as a mandatory syntax element. – William Robertson Jun 02 '16 at 10:36
  • 2
    @WilliamRobertson You should add your comment as an answer since this information is missing in the answers. – Roland Feb 06 '21 at 14:15

9 Answers9

385

I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.

There is a huge difference in SQL*Plus between the meaning of a / and a ; because they work differently.

The ; ends a SQL statement, whereas the / executes whatever is in the current "buffer". So when you use a ; and a / the statement is actually executed twice.

You can easily see that using a / after running a statement:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> drop table foo;

Table dropped.

SQL> /
drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

In this case one actually notices the error.


But assuming there is a SQL script like this:

drop table foo;
/

And this is run from within SQL*Plus then this will be very confusing:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> @drop

Table dropped.

drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

The / is mainly required in order to run statements that have embedded ; like CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE statements and for any BEGIN...END blocks.

Community
  • 1
  • 1
  • 2
    @amis, I'm new to Oracle and get into the same problem. This question is very useful, but all answers gave an explanation about the "why" not the "best way" to work or some way to workaround this. So, If I understood right, there is no way to have just one script and keep it usable for all tools... or do you discover some way? – ceinmart Nov 21 '13 at 14:08
  • 6
    @ceinmart: the "best way" is to define one (and only one) tool to execute SQL scripts - and the "correctness" of a script is validated using that tool. Similar to having one compiler for your programming language or one specific version of your runtime environment (Java 7, .Net 4.0, PHP 5.x, ...) –  Nov 21 '13 at 14:49
  • 3
    Good answer. Is it just me or is Oracle goofy and archaic compared to other DB's? I've used a lot of Sybase and it seems much more intuitive. – splashout Apr 10 '18 at 19:47
  • 1
    @splashout: well, if you want to run statements that contain the default delimiter (`;`) then you need to find a way to specify an alternate delimiter –  Apr 10 '18 at 20:09
  • 1
    Your statement: “The / is mainly required in order to run statements that have embedded ; like CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE statements and for any BEGIN...END blocks.” is the most useful statement on this whole page. – Manngo Nov 16 '21 at 23:55
115

I wanted to clarify some more use between the ; and the /

In SQLPLUS:

  1. ; means "terminate the current statement, execute it and store it to the SQLPLUS buffer"
  2. <newline> after a D.M.L. (SELECT, UPDATE, INSERT,...) statement or some types of D.D.L (Creating Tables and Views) statements (that contain no ;), it means, store the statement to the buffer but do not run it.
  3. / after entering a statement into the buffer (with a blank <newline>) means "run the D.M.L. or D.D.L. or PL/SQL in the buffer.
  4. RUN or R is a sqlsplus command to show/output the SQL in the buffer and run it. It will not terminate a SQL Statement.
  5. / during the entering of a D.M.L. or D.D.L. or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer"

NOTE: Because ; are used for PL/SQL to end a statement ; cannot be used by SQLPLUS to mean "terminate the current statement, execute it and store it to the SQLPLUS buffer" because we want the whole PL/SQL block to be completely in the buffer, then execute it. PL/SQL blocks must end with:

END;
/
Mr_Moneybags
  • 3,927
  • 3
  • 19
  • 15
  • You said that `;` cannot be used signify point (1) when it comes to PL/SQL, but why isn't `END;` sufficient to mark termination? Isn't that unambiguous? – ForgottenUmbrella May 09 '21 at 05:04
31

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.

dpbradley
  • 11,645
  • 31
  • 34
  • 10
    this answer doesn't explain why ```/``` or ```;``` see the answer of @a_horse_with_no_name or @Mr_Moneybags for more context – Kay Apr 06 '20 at 14:25
23

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>)
Anik Islam Abhi
  • 25,137
  • 8
  • 58
  • 80
jva
  • 2,797
  • 1
  • 26
  • 41
17

From my understanding, all the SQL statement don't need forward slash as they will run automatically at the end of semicolons, including DDL, DML, DCL and TCL statements.

For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.

Anik Islam Abhi
  • 25,137
  • 8
  • 58
  • 80
Jerry
  • 316
  • 5
  • 11
0

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.

Jonathan
  • 11,809
  • 5
  • 57
  • 91
  • So do you order things that require the / (such as subprograms and triggers) at the end? What if you have multiple triggers? I ran a test and only the first one executes unless it has a / between each one. Am I missing something? – amischiefr Jul 03 '09 at 16:34
  • I try to avoid it (if possible), but if i can't (like in triggers), I use the semicolons and slashs exactly as used in the official scripts that generate the samples schemas of oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14198/scripts.htm#Cihgfecd For the inserts problem, I try to separate the scripts that create objects from those who populate the tables. – Jonathan Jul 03 '09 at 19:12
  • 2
    Sorry, but this does not answer the question. – DerMike Jun 02 '17 at 13:03
0

use semicolon in sql script files to separate sql statements that tell client software (SQL*Plus, SQL Developer) what are the single statements to be executed.

use slash in sql script files to separate pl/sql blocks that tell client software (SQL*Plus, SQL Developer) what are the single pl/sql blocks to be executed.

use slash in SQL*Plus command line when you want to execute buffered statement (yes it is a single sql statement without the semicolon or pl/sql block without the slash).

Kamal SABBAR
  • 638
  • 5
  • 11
0

Use slash after statements that end with "end;", otherwise do not use it.

tenlys
  • 1
0

Before creating the object type in Oracle, dummy types are created to reference other types that have not yet been defined. Slash is used to executing the most recent type definition changes, or to replace the existing type in the SQL buffer with the replaced type.

References: https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_twelve004.htm

Yukesh Marudhasalam
  • 1
  • 1