Forum Stats

  • 3,733,281 Users
  • 2,246,740 Discussions
  • 7,856,645 Comments

Discussions

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

3278558
3278558 Member Posts: 27
edited October 2016 in SQL & PL/SQL

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..

authid_current_usermathguyWilliam RobertsonPaulzipjaramillKayKDejan T.BPeaslandDBASven W.Goran StefanovićJarkko TurpeinenBluShadowKalpataru

Best Answer

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited October 2016 Accepted Answer

    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)

«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited October 2016
    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.

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited October 2016

    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

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited October 2016

    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.

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited October 2016

    Dear @3278558,

    kindly refer the following dear..

    https://docs.oracle.com/cd/B13789_01/mix.101/b12039/idx-sym.htm#index-;

    Thanks and Regards,

    Vysakh Suresh

  • mathguy
    mathguy Member Posts: 9,770 Gold Crown
    edited October 2016

    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).

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited October 2016 Accepted Answer

    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)

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,259 Red Diamond
    edited October 2016

    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.

    William Robertson
  • Unknown
    edited October 2016

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

    mathguy
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited October 2016

    @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

  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited October 2016

    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.
  • Unknown
    edited October 2016
    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#SQLRF01604Syntax 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;   
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited October 2016
    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

  • mathguy
    mathguy Member Posts: 9,770 Gold Crown
    edited October 2016

    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

  • Unknown
    edited October 2016
    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!

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,259 Red Diamond
    edited October 2016
    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 characterORA-06512: at line 4SQL>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 selectedSQL> select sql_id, sql_fulltext from v$sql where sql_fulltext like '%;' and rownum = 1;SQL_ID        SQL_FULLTEXT------------- --------------------------------------------------------------------------------2gtvv7rb645kb begin                NULL;              end;SQL>
    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited October 2016
    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.

  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    edited October 2016
    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.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited October 2016

    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

  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    edited October 2016

    Thanks Brian,

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

    Dejan T.BPeaslandDBASven W.
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited October 2016

    Perfect! Thanks so much!

  • 3278558
    3278558 Member Posts: 27
    edited October 2016

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

  • 3278558
    3278558 Member Posts: 27
    edited October 2016

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

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited October 2016

    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

  • mathguy
    mathguy Member Posts: 9,770 Gold Crown
    edited October 2016

    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.

This discussion has been closed.