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