Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Some statements like create table, insert into etc take a semicolon at the end:

CREATE TABLE employees_demo
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) 
         CONSTRAINT emp_last_name_nn_demo NOT NULL
    ) ;

while others like

set echo on
connect system/manager

go through without the semicolon as well.

What is the reasoning behind this? How can I decide myself where to put the semicolon and where not?

share|improve this question
6  
+1 To make things more confusing, you can use / on the following line instead of the ; in some cases. –  bernd_k Jan 9 '11 at 8:34

4 Answers 4

up vote 14 down vote accepted

Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon

Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.

share|improve this answer
    
The semicolon at the end of a command is the same as a GO statement at the end of a command executed in OSQL/SQLCMD in Sql Server. It's the sign the command will be parsed and executed. –  Marian Jan 21 '11 at 18:34

When you're entering an SQL statement into SQL*Plus, it needs to know when you're done with it, especially if the command spans multple rows. Therefore, it requires a terminal character character which can be set with the set sqlterminator. By default, this character is the semicolon:

SQL> select *
  2  from
  3  dual;

D
-
X

Now, changing this terminal character to a #:

SQL> set sqlterminator #
SQL> select *
  2  from
  3  dual#

D
-
X

The SQL statement was ended (end executed) with a #.

You ask: why was set sqlterminator # not followed by a semicolon? Answer, because this is not an SQL statement. Statements that relate to SQL*Plus and its behaviour, output, connection asf (such as set echo on and connect system/manager) are not SQL statements and are therfore entered without the semicolon.

What has this to do with the /?

When you entered an SQL statement, SQL*plus filled something what it calls a buffer. This buffer can be shown with the list command:

SQL> list
  1  select *
  2  from
  3* dual
SQL>

(Note: I only entered list, the rest is returned)

The / now executes what is currently in the buffer. Lets try that:

SQL> /

D
-
X

As can be seen, the same query is executed.

share|improve this answer

The semicolon is the sequence point for the SQL parser, whereas commands that are executed within SQL*Plus are executed immediately anyway. As an analogy, compare the semicolons in a C program to the commands entered into the shell.

share|improve this answer

The way I understand it is that the general SQL parser shares a lot in common with the general javascript parser, in that it would like a semicolon after every appropriate place, but it's not required except in certain circumstances. When I was taught to write SQL, I was told a semicolon was part of the language, not an extra operator we could ignore.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.