1 2 3 Previous Next 31 Replies Latest reply on Oct 21, 2016 1:18 PM by Jarkko Turpeinen

    When should I use semicolon(;) or slash(/) in oracle

    3278558

      Hi

       

      When should I use semicolon(;) or slash(/) in oracle?

       

      It depends of the environment: sqlplus, toad, pl\sql developer

       

      or OS: Windows, linux

       

      Since, I tested that, and I can execute any statement only with (;) in any environment and operative system..

        • 1. Re: When should I use semicolon(;) or slash(/) in oracle
          John Thorton

          3278558 wrote:

           

          Since, I tested that, and I can execute any statement only with (;) in any environment and operative system..

          So why did you post?

          You have your answer.

          • 2. Re: When should I use semicolon(;) or slash(/) in oracle
            TPD-Opitz

            as with every douplicated feature in any language the quiestion arises if there are drawbacks with one or the other. Therfore I find the OPs question very valid.

             

            Eg. try this:

             

            create table my_test ( test_id number(3) not null unique key ); -- not tested, sorry

            insert into my_test values(1)/

            insert into my_test values(2);

            ; -- syntax error

            / -- unique constraint violation

             

            as you see the meaning of / depends on the lines of code before it.

            In a single line it repeats the previous SQL statement.

             

            therefore I'd always prefer ; since it fails for the right reason.

             

            bye

            TT

            • 3. Re: When should I use semicolon(;) or slash(/) in oracle
              BrunoVroman

              Hello,

               

              note that in some cases you need both!

              CREATE OR REPLACE TRIGGER mytrigger AS ...

              ...

              END;

              /

               

              As well for "CREATE TYPE", ... 

               

              Best regards,

               

              Bruno Vroman.

              • 5. Re: When should I use semicolon(;) or slash(/) in oracle
                mathguy

                It does not depend on the operating system (Windows vs Linux).

                It does not depend (too much) on the interface (SQL*Plus, Toad, SQL Developer).

                 

                SQL statements are ended by semicolon (;).  I am still not clear, from all the documentation, whether the ; is required by the SQL Standard (the standard itself is not freely available, so I haven't seen it). From a draft of the standard I believe ; is NOT required by the standard. Rather, it is used by the interface (like SQL*Plus) to know where a SQL statement ends so it can send it to the compiler. The compiler receives the SQL statement (a string of characters) without the ;

                 

                (Make a mental note  -  this may not be exactly right, perhaps the ; is actually required by the SQL standard and the ; is part of the SQL statement).

                 

                You use / in PL/SQL, the procedural programming language developed to work very closely with Oracle SQL. EDIT: As others have pointed out, the slash is not part of the PL/SQL language; it is a SQL*Plus marker. The PL/SQL compiler does NOT receive the / with the rest of the code. I read Steven Feuerstein's PL/SQL book, and I just checked - he does say this explicitly; I just forgot.   In a PL/SQL procedure, for example, each statement within the procedure must end with a semicolon (;). You indicate the end of the procedure code with a forward slash /  Then the interface (like SQL*Plus) may have other uses for /  -  for example, in SQL*Plus, entering / by itself followed by Enter repeats the most recent database statement (ignoring SQL*Plus commands).

                 

                The bottom line is, unless and until you start writing PL/SQL code, you only use ;  -  and you may use / in SQL*Plus as a SQL*Plus command (to repeat the most recent SQL statement).

                • 6. Re: When should I use semicolon(;) or slash(/) in oracle
                  Jarkko Turpeinen

                  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)

                  • 7. Re: When should I use semicolon(;) or slash(/) in oracle
                    Billy~Verreynne

                    SQL statements do not have end-of-statement terminators, like a semi-colon.

                     

                    PL/SQL is a procedural language, and like Ada and Pascal and other programming languages (where a program unit consists of multiple program statements), uses a semi-colon as end-of-statement character. COBOL for example uses a period. Etc.

                     

                    SQL*Plus uses special characters to determine what it needs to send to the Oracle db server as source code statements. It uses semi-colon and forward-slash characters as this "send text to Oracle" instruction. Thus the confusion at semi-colons are now both used by the client (SQL*Plus) and the server (PL/SQL code).

                     

                    SQL*Plus has a very primitive parser though to know when a semi-colon is used to end a SQL command - and send the SQL text to Oracle as semi-colon means "send text now". And when text entered is part of a PL/SQL block of text, and semi-colons need to be ignored as it is part of the PL/SQL code, and not "send text now" instructions to PL/SQL.

                    • 8. Re: When should I use semicolon(;) or slash(/) in oracle
                      BPeaslandDBA

                      When I first started at my current company, I saw lots of code from dev's that got the slash wrong. The slash is purely a SQL*Plus thing. If you're using anything else to interact with the database, the slash really isn't even needed. My devs work with PL/SQL Developer from All Around Automations and they never use a slash in their PL/SQL blocks. They press Run and away it goes. This is why they got confused about the slash.

                       

                      Now even the semi-colon may not be needed. I've written plenty of code submitting a SQL statement to the database that never had a semi-colon in it. So it can be confusing when to need a semi-colon and/or slash as statement terminators. I wrote this wiki article for our devs a long time ago and maybe it can help you.


                      Cheers,
                      Brian

                       

                       

                      Wiki article:

                       

                      Slash or No Slash

                       

                      SQL scripts that are deployed are executed in Oracle's SQL*Plus command-line utility, whereas many developers use a GUI tool like PL/SQL Developer. The forward slash means something to SQL*Plus that is not needed in PL/SQL Developer. As such, it can be confusing to know if you need to include a forward slash in your SQL scripts or not. Hopefully, this section will shed some light on what the forward slash does, when to use it, and when to not use it.

                      Semi-colon Terminator

                      For most SQL statements, the semi-colon is the statement terminator. For example, consider this simple SQL statement run in SQL*Plus:

                       

                      SQL> select sysdate from dual;

                      SYSDATE
                      ---------
                      18-JUN-12

                      When SQL*Plus sees the semi-colon, it knows the end of the SQL statement has been reached and it can now execute the command.

                      SQL*Plus Buffer

                      You may not be aware that SQL*Plus has a buffer for its commands. If I press the 'l' key for 'list', then I can see the command currently in my session's buffer.

                      SQL> l
                        1* select sysdate from dual

                      Not surprisingly, there is the command I just executed. I then executed another SQL statement and here is how my buffer now looks:

                      SQL> l
                        1  select sysdate,user
                        2* from dual

                      As you can see, I now have two lines in my session's SQL*Plus buffer.

                      Slash = Execute Buffer

                      The first rule to understand about the forward slash is that to SQL*Plus, the forward slash means to execute the contents of the buffer. To illustrate this concept, I will execute a SQL statement, wait a few seconds, and then just execute that same SQL statement again but just executing the buffer.

                       

                      SQL> select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') from dual;

                      TO_CHAR(SYSDATE,'MM
                      -------------------
                      06/18/2012 15:20:40

                      SQL> /

                      TO_CHAR(SYSDATE,'MM
                      -------------------
                      06/18/2012 15:21:17

                      SQL> /

                      TO_CHAR(SYSDATE,'MM
                      -------------------
                      06/18/2012 15:21:50

                       

                      You can see that all I did the second and third time was to just type '/' and hit enter and SQL*Plus executed the contents of its command buffer each time.

                       

                      PL/SQL Blocks

                      The semi-colon statement terminator and forward slash to execute the command buffer's contents worked just fine until Oracle introduced PL/SQL in Oracle version 7. The problem is that PL/SQL blocks can have multiple semi-colons to terminate the individual statements that make up that block. Consider this very simple PL/SQL block that does nothing:

                      SQL> begin
                        2  null;
                        3  null;
                        4  end;
                        5

                      Lines 2 and 3 contain perfectly valid statements that are each terminated with the semi-colon. And in line 4, we have the END keyword signifying the end of the PL/SQL block. If we were not allowed nested BEGIN/END pairs, then every time SQL*Plus sees "END;" it would know the end of the PL/SQL block has been reached. But we are allowed nested BEGIN/END pairs so the following is perfectly legal and valid:

                      SQL> begin
                        2     begin
                        3       null;
                        4     end;
                        5     null;
                        6  end;
                        7

                       

                      You can tell from the above that just looking for "END;" is not enough because SQL*Plus would have tried to run the block after line 4. So how did Oracle decide to signify the that the PL/SQL block was ready to execute? The answer is by using the forward slash as you may already be aware. The second rule to understand is taht all the forward slash is doing when you use it to end a PL/SQL block is to tell SQL*Plus to run what is in the buffer! This has not changed since before PL/SQL was created for Oracle 7.

                      Consider the following sample:

                       

                      SQL> begin
                        2  null;
                        3  end;
                        4  /

                      PL/SQL procedure successfully completed.

                      SQL> l
                        1  begin
                        2  null;
                        3* end;

                      On line 4, I typed the forward slash to execute the PL/SQL block. You can see that my block successfully completed. If we go back and look at the contents of my command buffer, you can see it contains everything but the forward slash. The forward slash is not part of the command buffer.

                      So now, I'll run a different PL/SQL block:

                       

                      SQL> begin
                        2     dbms_output.put_line('Today is '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
                        3  end;
                        4  /
                      Today is 06/18/2012 15:39:32

                      PL/SQL procedure successfully completed.

                      The forward slash said to SQL*Plus to run what is in its buffer, and the results are displayed. Now let's type just the slash again and we should see our PL/SQL block get executed again.

                       

                      SQL> /
                      Today is 06/18/2012 15:40:42

                      PL/SQL procedure successfully completed.

                       

                      I did not have to type my PL/SQL block anew as it is currently in the command buffer.

                      PL/SQL Developer and PL/SQL Blocks

                      The biggest problem for most developers is that PL/SQL Developer does not require you to use the forward slash. Why? Because you can hit Execute (F8) to run your PL/SQL block. PL/SQL Developer knows that the moment you hit F8, you are intending to submit the PL/SQL block to be executed. In this case, F8 in PL/SQL Developer is doing the same job as the forward slash in SQL*Plus. But the Build & Deploy teams does not deploy code with PL/SQL Developer. They use SQL*Plus because scripting multiple executions is easier with a command line tool. Many developers make the mistake of not including the forward slash for PL/SQL blocks in scripts because they do not need it, but if you want to deploy that code section in a SQL script, the forward slash is required at the end of each PL/SQL block.

                      When Not To Use Slash

                      So we've seen when and why we use the forward slash, but when is it bad to to use it? The third rule to know is that it is bad to use the forward slash following a single SQL statement (not in a PL/SQL block), especially when that slash immediately follows a DML statement (INSERT, UPDATE, or DELETE). If my script contains the following:

                      select sysdate from dual;

                      /

                      Then I will get "double output" which is not what I normally intend to do in a script. I really only want one line returned, not two as the above script would do:

                       

                      SQL> select sysdate from dual;

                      SYSDATE
                      ---------
                      18-JUN-12

                       

                      SQL> /

                      SYSDATE
                      ---------
                      18-JUN-12

                       

                      It is even worse when I use the forward slash following a DML statement because that statement will get executed twice. Consider the following script:


                      insert into test_tab values (10);

                      /

                       

                      We now know that when I execute the two lines above in a script, SQL*Plus will execute it once due to the semi-colon statement terminator and then execute a second time because the forward slash tells SQL*Plus to run what is in the command buffer. When I the two-line script above, I get the following output:

                       

                       

                      SQL> insert into test_tab values (10);

                      1 row created.

                      SQL> /
                      insert into test_tab values (10)
                      *
                      ERROR at line 1:
                      ORA-00001: unique constraint (PEASLAND.SYS_C00767176) violated

                       

                      Oops! The first insert worked (1 row created.) but when the forward slash was entered, SQL*Plus tried to insert the same data and I got caught on a unique constraint violation.

                       

                      Conclusion

                       

                      Hopefully, this page shows why the forward slash is needed, what is does, and when not to use it. To recap:

                       

                      • Include the forward slash at the end of each PL/SQL block
                      • Do not include the forward slash after any SQL statements not in a PL/SQL block.
                      • The forward slash after a single DML statement will cause that DML command to execute twice.
                      • 9. Re: When should I use semicolon(;) or slash(/) in oracle

                        +1 - I suggest you ask BluShadow to add that to the FAQ so it can be a permanent future reference.

                        • 10. Re: When should I use semicolon(;) or slash(/) in oracle
                          BPeaslandDBA

                          BluShadow, if you feel the urge, you can certainly add the wiki entry I wrote over 4 years ago some any FAQ you deem appropriate.

                           

                          Cheers!

                          Brian

                          • 11. Re: When should I use semicolon(;) or slash(/) in oracle
                            William Robertson

                            Just in case this documentation question has not yet been completely done to death:

                             

                            • The SQL language has no semicolon or any other command statement terminator character. (Edit: thanks for correction mathguy, though I'm not sure it makes much difference in SQL.)
                            • The PL/SQL language coincidentally uses a semicolon as a mandatory command statement terminator. It does not have any separate block terminator.
                            • SQL*Plus (a command-line tool for issuing commands and running scripts) allows you to define a terminator character. The default value for SQLTERMINATOR is ";" but you could alternatively use most other punctuation characters, or none.
                            • SQL*Plus uses the "/" (slash) character on its own line to execute the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.
                            • Other tools such as SQL Developer, PL/SQL Developer or (if you must) TOAD emulate SQL*Plus behaviour for compatibility reasons and therefore implement to semicolon as a terminator in some contexts.
                            • 12. Re: When should I use semicolon(;) or slash(/) in oracle

                              Just in case this documentation question has not yet been completely done to death:

                               

                              • The SQL language has no semicolon or any other command terminator character.

                              Please post a link to the docs supporting that statement.

                               

                              Both the Oracle docs and the sql-92 standard state just the opposite:

                              http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

                              X3H2-92-154/DBL CBR-002

                                5.1 <SQL terminal character>

                               

                                | <plus sign>

                                | <comma>

                                | <minus sign>

                                | <period>

                                | <solidus>

                                | <colon>

                                | <semicolon>

                                | <less than operator>

                                | <equals operator>

                                | <greater than operator>

                                | <question mark>

                                | <underscore>

                              Search that doc and you will see MANY other similar references.

                              https://docs.oracle.com/database/121/SQLRF/statements_9014.htm#SQLRF01604

                              Syntax

                              insert::=

                              Description of insert.gif follows

                              https://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708

                              Syntax

                              update::=

                              Description of update.gif follows

                              https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702

                              Syntax

                              select::=

                              Description of select.gif follows

                              All three basic DML syntax diagrams show semicolon as the terminator

                               

                              Table 2-2 in the PL/SQL Language doc shows semicolon as a statement terminator

                              https://docs.oracle.com/database/121/LNPLS/fundamentals.htm#CBJIIIEA

                              ;   Statement terminator

                              • 13. Re: When should I use semicolon(;) or slash(/) in oracle
                                jaramill

                                BPeaslandDBA wrote:

                                 

                                 

                                Hopefully, this page shows why the forward slash is needed, what is does, and when not to use it. To recap:

                                 

                                • The forward slash after a single DML statement will cause that DML command to execute twice.

                                This last bullet point is KEY!!  Basically the forward slash "/" is equivalent to re-executing the command that is currently in the buffer.

                                Good writie-up BPeasland

                                • 14. Re: When should I use semicolon(;) or slash(/) in oracle
                                  mathguy

                                  Hi,

                                   

                                  You may be mixing up two unrelated concepts: statement terminator (such as ; in C or PL/SQL - William wrote "command" terminator but he probably meant "statement" terminator), which is different from terminal character or terminal symbol (a concept used in formal grammars). A terminal character is one that stands for itself and is not expanded/resolved into something else by the language's production rules. The SQL Standard (or, rather, the document you posted - which is an Informal Review Draft, not the final Standard) does talk specifically about an "SQL terminator", which can be END-EXEC, semicolon or right paren - but only in the context of embedded SQL statements. It makes no other mention of "SQL terminator".

                                   

                                  The railroad diagrams for SELECT and INSERT and UPDATE are more convincing, but they may be a documentation error, as illustrated by the following SQL*Plus session screenshot:

                                   

                                  SQL> create table t1 ( c number );

                                  Table created.

                                  SQL> insert into t1 values (0);

                                  1 row created.

                                  SQL> l
                                    1* insert into t1 values (0)
                                  SQL> /

                                  1 row created.

                                  SQL>

                                   

                                  Notice that the statement is saved in the buffer with no ; terminator, and it is executed OK from the buffer. Although we know sometimes Oracle gives us a break - perhaps the Oracle definition, which may or may not concord with the SQL Standard in this respect, does require a semicolon terminator but will accept a statement without it without throwing a syntax error. (The SQL*Plus buffer may not be convincing - perhaps SQL*Plus adds the semicolon before sending to the database; but the SQL text saved in the catalog tables doesn't have the semicolon either.)    In any case, I would not take Oracle syntax as definitive proof of what the SQL Standard requires (regarding ANY feature of the language).

                                   

                                  I don't think there is any dispute about PL/SQL requiring ; as a statement terminator.

                                   

                                  Cheers,    -    mathguy

                                  1 2 3 Previous Next