1 2 3 Previous Next 31 Replies Latest reply on Oct 21, 2016 1:18 PM by Jarkko Turpeinen Go to original post
      • 15. Re: When should I use semicolon(;) or slash(/) in oracle

        You may be mixing up two unrelated concepts

        And I think that YOU are drawing unwarranted conclusions.

         

        I have tried to explain the basic concept of trouble-shooting to you several times in the past and the result is usually you thinking I am being rude or judgmental - neither of which is true IMHO.

         

        That basic concept consists of (at least) three things:

         

        1. assume NOTHING

        2. base opinions on FACTS - don't substitute your own personal opinion for facts.

        3. when you don't understand for certain what someone means ASK THEM - don't use your own interpretation/meaning.

         

        Let's apply those three things to what I said and then what you replied with:

        You may be mixing up two unrelated concepts

        If you reread my reply CAREFULLY you should see that I did NOT state my opinion or thoughts.

         

        I ask the responder to provide ANY INFO that supports the statements they made. I have no idea whether what the responder said is just their opinion or if they are stating it as fact. In either case I have no idea what they based their opinion/statement on.

         

        I then posted links and doc quotes that, to me, say the EXACT OPPOSITE of what the responder said. I did that to show that I could find/post info to support my request for them to explain what they posted.

         

        Specifically I did NOT say I disagreed (or agreed) with what they said. But rather I posted doc references that appear to contradict what they said.

         

        So far I haven't even presented any 'concept'. My post is nothing more than the equivalent of:

        1. You said 'A'

        2. These published docs say 'B'

        3. Explain the discrepancy

        William wrote "command" terminator but he probably meant "statement" terminator)

        Hopefully you can now see the value of ASSUME NOTHING? I have no idea what he 'probably meant'. I want facts - so I ask them to provide some facts. To illustrate the type of facts I was referring to I provided some facts in the form of links to docs and quotes from those docs.

         

        Refer back to #1 of 'concepts of troubleshooting' above.

         

        ASSUME NOTHING - making 'assumption's is probably the BIGGEST mistake people do when troubleshooting or analyzing an issue. The ONLY correct places for an 'assumption' are: 1) as part of an initial hypotheses or 2) as part of a solution when it is not possible to gather the requisite facts.

         

        So when you simply MUST make a decision on limited information you must often base that decision on your own opinions (hopefully based on prior experience) and some basic (hopefully realistic) assumptions.

        The railroad diagrams for SELECT and INSERT and UPDATE are more convincing, but they may be a documentation error

        May be? Is that an opinion? Or another 'assumption'?

         

        See what I mean about the quagmire of 'assuming'.

         

        Of course the docs could be wrong. So let's consider some more FACTS:

         

        1. Those are the official docs and are published by a reputable company

        2. That company has every incentive to make the docs accurate

        3. The docs have had that same info in them for decades

         

        Can the docs be wrong? Of course - they often are.

         

        But for me they certainly carry a lot more weight than someone's mere opinion - no matter who they are.

         

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

         

        In general I don't think there is either. But there appears to be some 'dispute' among at least some of the responders in this thread.

         

        So as I generally do I FIRST go to the docs to see what they say and I posted that info so others can: 1) see that the docs really do address that point

        2) read the info for themselves in the full context

        3) see that the docs do NOT use the term 'terminator' - rather they use 'delimiter'.

         

        And again - I think providing that doc link and quote should carry much more weight than my just stating my opinion.

         

        Consider this that you said in your first reply:

        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 /

        Is it your opinion that 'In a PL/SQL procedure' that you 'indicate the end of the procedure code with a forward slash'?

         

        I doubt if that is what you meant to say - but it certainly can be read that way.

         

        In the forums anyone can respond regardless of their knowledge or experience.

         

        And anyone can offer opinions.

         

        The difficulty comes with trying to assign a worth/value to what someone posts. I prefer facts to opinions so I prefer knowing what statements are being based on.

         

        But hey - YMMV!

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

          rp0428 wrote:

           

          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

           

          What William implied (and what I posted) is easily demonstrated:

           

          SQL> declare
            2          i       integer;
            3  begin
            4          execute immediate 'select count(*) from user_objects;'
            5                  into i;
            6  end;
            7  /
          declare
          *
          ERROR at line 1:
          ORA-00911: invalid character
          ORA-06512: at line 4
          
          
          SQL>
          SQL> declare
            2          i       integer;
            3  begin
            4          execute immediate 'select count(*) from user_objects'
            5                  into i;
            6  end;
            7  /
          
          PL/SQL procedure successfully completed.
          
          SQL>
          

           

          Thus the manual is wrong. A SQL statement like a SELECT cannot be  terminated with a semi-colon. As shown by the execution of native SQL statements above.

           

          Unless the manual is implying that the syntax shown is typical SQL syntax from a SQL client - and not "native" SQL.

           

          Simply looking at the SQL Shared Pool shows no SQL statements with semi-colon end-of-statement characters. Only PL/SQL code blocks:

           

          SQL> select sql_id, sql_fulltext from v$sql where sql_fulltext like '%;' and trim(upper(sql_fulltext)) not like 'DECLARE%' and trim(upper(sql_fulltext)) not like 'BEGIN%';
          
          no rows selected
          
          SQL> select sql_id, sql_fulltext from v$sql where sql_fulltext like '%;' and rownum = 1;
          
          SQL_ID        SQL_FULLTEXT
          ------------- --------------------------------------------------------------------------------
          2gtvv7rb645kb begin
                          NULL;
                        end;
          
          
          SQL>
          
          • 17. Re: When should I use semicolon(;) or slash(/) in oracle
            William Robertson

            rp0428 wrote:

             

            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.

            I wish I knew what a "terminal character" was. I've never heard of it before, it doesn't seem to be a standard computer science term, and the document doesn't define it. I can't see much in common between the 12 symbols listed. "SQL terminator" however is mentioned in the context of embedding SQL statements within a host language.

             

            It is odd that the documentation includes ';' in the syntax diagrams, when as others have mentioned it is clearly invalid in dynamic SQL and unnecessary in SQL*Plus.

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

              BPeaslandDBA wrote:

               

              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

               

               

              Unfortunately, due to the automatic archiving of threads, the FAQ is archived and can't be added to (I can only edit existing entries).

               

              If you have the article in a PDF, I can have a word with the admin to see about getting that uploaded to the PL/SQL and SQL documents section on your behalf.

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

                I'll attach the PDF to this response. When it gets added, please reply back to this thread with links to the doc(s).

                 

                Thanks!

                Brian

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

                  Thanks Brian,

                  I've uploaded it to the documents section...

                   

                  When to use a semicolon or a slash in PL and SQL?

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

                    I understand, but what are the advantages of use "/" (Execute commands stored in the SQL buffer).

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

                      I understand, but what are the advantages of use "/" (Execute commands stored in the SQL buffer).

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

                        Not sure you have understood. / allows you to run the previous sql command again and again, which is highly useful when you're waiting for the results of a select to change for example. You also need it to run pl/SQL blocks.

                         

                        Edit: and both of those points are explained in detail, with examples in BPeaslandDBA's post

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

                          I'll give you just one small example out of many. I run a simple SQL query. I look at the output, and the ROW_ID column is chopped to three characters, which is fine for the values but not for the column name. The DESCRIPTION column is formatted to be 100 characters, which pushes other columns past the limit and I get an unreadable, wrapped output. Especially annoying when I want to copy the results and post them on this site.


                          I execute COLUMN ... FORMAT ...  commands, then I hit  /   to rerun the query from the buffer. I expect exactly the same result, but now it will be formatted nicely.

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

                            mathguy wrote:

                             

                            I execute COLUMN ... FORMAT ... commands, then I hit / to rerun the query from the buffer. I expect exactly the same result, but now it will be formatted nicely.

                            And to quote Math Guy's comment, he executed a SQL* Plus command yet the backslash only executes the previous SQL command in the buffer.  SQL* Plus commands are NOT stored in the buffer.  That's the advantage of the backslash.

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

                              3278558 kirjoitti:

                               

                              I understand, but what are the advantages of use "/" (Execute commands stored in the SQL buffer).

                              When using script files. You need to separate multiple pl/sql blocks from each other and from sql statements.

                               

                              When SQLPlus executes script (by start or @ or @@), it will read script file content into buffer until it faces slash / or semicolon and sends (syntax is perfect) that staff to server to be executed as single statement / block. When it faces a dot ., it will stop reading into buffer but does not send statement / block over server.

                               

                              If there is none of the dot or slash / or ; then it reads whole content into buffer and nothing happens.

                               

                              Demo

                               

                              /* test.sql */
                              begin
                                null;
                              end;
                              
                              begin
                                null;
                              end;
                              
                              select * from dual
                              
                              select * from dual
                              

                               

                              I run script three times  by SQLPlus but nothing happens.

                               

                              ?!

                               

                              I look the buffer contents and see two blocks and two sql statements in the buffer.

                               

                              SQL> start test
                              SQL> start test
                              SQL> start test
                              
                              SQL> list
                                1  begin
                                2    null;
                                3  end;
                                4
                                5  begin
                                6    null;
                                7  end;
                                8
                                9  select * from dual
                               10
                               11  select * from dual
                               12
                               13
                               14*
                              SQL>
                              

                               

                              i can't run that, because it is not single statement.

                               

                              Well i don't believe that and run it.

                               

                              SQL> /
                              begin
                              *
                              ERROR at line 5:
                              ORA-06550: line 5, column 1:
                              PLS-00103: Encountered the symbol "BEGIN"
                              

                               

                              okay, i add slash after every pl/sql block and semicolon after every sql statements.

                               

                              /*test.sql*/
                              begin
                                null;
                              end;
                              /
                              begin
                                null;
                              end;
                              /
                              select * from dual
                              ;
                              select * from dual
                              ;
                              

                               

                              i start that.

                               

                              SQL> start test
                              
                              PL/SQL procedure successfully completed.
                              
                              PL/SQL procedure successfully completed.
                              
                              D
                              -
                              X
                              
                              D
                              -
                              X
                              
                              SQL> list
                                1  select * from dual
                                2*
                              SQL>
                              

                               

                              Notice that buffer contains only last statement from the script file.

                               

                              Advantage over what? There's not any advantage except that you don't have to write run to send buffer content to server.

                               

                              Edit: Notice also that run is not allowed command in script file.

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

                                Jarkko Turpeinen wrote:

                                 

                                 

                                Edit: Notice also that run is not allowed command in script file.

                                Really?

                                 

                                SQL> ho type test.sql
                                col message format a7
                                
                                select 'Hello' as message from dual
                                .
                                
                                run
                                
                                SQL> @test.sql
                                  1* select 'Hello' as message from dual
                                
                                MESSAGE
                                -------
                                Hello
                                
                                1 row selected.
                                
                                SQL>
                                

                                 

                                Perhaps I misunderstood what you meant.

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

                                  Not really!

                                  Perhaps I misunderstood what you meant.

                                  surely you did not

                                   

                                  run works in script files but it needs the dot to stop reading stuff into buffer. I missed that in my tests. Sorry community!

                                   

                                  SQL> l
                                    1  begin
                                    2    null;
                                    3  end;
                                    4  .
                                    5  run
                                    6  begin
                                    7    null;
                                    8  end;
                                    9  .
                                   10  run
                                   11  select * from dual
                                   12  /
                                   13  select * from dual
                                   14  .
                                   15  run
                                   16*
                                  
                                  SQL> start test
                                    1  begin
                                    2    null;
                                    3* end;
                                  
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  
                                    1  begin
                                    2    null;
                                    3* end;
                                  
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  
                                  
                                  
                                  D
                                  -
                                  X
                                  
                                  
                                    1* select * from dual
                                  
                                  
                                  D
                                  -
                                  X
                                  
                                  
                                  SQL>