Why I do not need semicolon after some of Oracle SQL commands?

I encountered interesting behavior in SQL*Developer. Since I am preping to an exam I would like to understand some inconsistencies related to requirement of semicolons.

As you know - Oracle DB in their documentation seems very strict about usage of their semicolons and terminating statments. Yet some of the commands do not require semicolons at all. Why is it that?

DML: -- have to have a semicolons:

SELECT .... FROM ...; -- has to have a semicolon
CREATE TABLE x (...); -- has to have a semicolon

TCL: -- do not require semicolons:

SAVEPOINT
ROLLBACK

SQL*Plus:

DESC TABLE tab1 -- does not require semicolons.

Why is it so inconsistent [at least in SQL*Developer] and what is the rule here?

Solution 1:

Your front-end drives the requirement, not the commands themselves. (If you check out the documentation, you won't find them in the syntax diagram for commands.) I'll focus on SQL*Plus below; SQL*Developer is likely similar, though one of your examples demonstrates at least one difference.

Many commands to be serviced by the database can span multiple lines, so sqlplus uses the semicolon as an indicator as to when you're done entering your SQL statement, at which point it gets sent to the database (sans terminating semicolon). That's the case with the DML (SELECT), DDL (CREATE TABLE), & TCL (SAVEPOINT, ROLLBACK) examples. You could also use a line with nothing but a slash to do the same, or use a line with nothing but "." to terminate the statement but not send it to the database.

In contrast, some commands are directed at the front-end itself instead of the database, such as you sqlplus DESC (DESCRIBE) example. In those cases the front-end is smart enough to recognize and process them without explicit termination with a semicolon, often because the front-end recognizes only single-line commands. (I'm not counting ending the line with "-" to indicate it carries over onto the next line.)

PL/SQL is another ball of wax: statements within it end with semicolons, so if sqlplus recognizes that you started a plsql block, then the normal semicolon behavior is suppressed, and you must use "/" or "." to finish the block.

AN EDIT ADDED YEARS LATER PER REQUEST FROM Laxmi Agarwal :

It's good to keep in mind that there's a definite line between the front-end you use to compose/edit/submit SQL commands, and the backend database that processes your commands. You write & submit a command from the front-end; that front-end handles the contact with the backend & reads the response; and finally the front-end presents the results, be it data from a query or a status like "Table altered".

A bit reminiscent of how the "vi" text editor operates, sqlplus has different modes it can be in. When you start sqlplus, it's in "command" mode, in which you enter sqlplus commands (this is different from SQL !). Recognized commands include COLUMN, SET, and DESCRIBE, plus several others. One special command in this mode is a / on its own line, which means "send the text in the buffer to the database for execution"; more on that below. Semicolons play almost no role in this mode; in most cases you may end a command with one, but it's silently ignored.

The 2nd mode that sqlplus can be in is "pl/sql" mode. You enter this mode with a BEGIN or DECLARE keyword, and sqlplus will then happily start reading whatever you type into its buffer. Semicolons get read as a normal part of whatever you type, because they're part of the language. You can exit this mode with a . on its own line, which will drop you back into command mode. For convenience, you can instead enter a / which is a shortcut to 1) drop back into command mode and 2) execute the buffer.

The 3rd mode is "sql" mode. You enter this mode by starting any SQL statement: SELECT, CREATE, ALTER, etc. As with pl/sql mode, what you type gets loaded into the buffer; and as before, you can drop back into command mode with a ., or use the same / shortcut as above to immediately execute the buffer.

There's an additional shortcut in sql mode. A semicolon ending a line does 4 steps in one shot: append the current line to buffer, strip the final semicolon from the buffer, enter command mode, and execute the buffer. This a super-convenient, and possible only because a semicolon is not part of SQL (in contrast to PL/SQL, in which a semicolon separates statements).

Disclaimer: I no longer have immediate access to SQL*Plus or SQL*Developer to test what I wrote above, so I'm going from memory. This information was (is?) true for SQL*Plus, yet probably also applicable to SQL*Developer - at least for the most part.