When do I need to use a semicolon vs a slash in Oracle SQL

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

User · Answer

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

User · Answer

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

User · Answer

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  lt values to be inserted gt  FROM dual WHERE NOT EXISTS  SELECT                    FROM my table                   WHERE  lt identify data that you are trying to insert gt

User · Answer

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

User · Answer

I wanted to clarify some more use between the   and the    In SQLPLUS      means  terminate the current statement  execute it and store it to the SQLPLUS buffer   lt newline gt  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    after entering a statement into the buffer  with a blank  lt newline gt   means  run the D M L  or D D L  or PL SQL in the buffer  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    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

User · Answer

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 gt  drop table foo   Table dropped   SQL gt    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 gt   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 a CREATE PROCEDURE statement

[sql] When do I need to use a semicolon vs a slash in Oracle SQL?

Examples related to sql

Examples related to oracle

Examples related to sqlplus