Oracle: when do I use a slash between statements?

Question

I have finally traced a dreaded ORA-00933: SQL command not properly ended. error to not putting a slash between statements.

If I attempt to run this:

SELECT 200 AS integer_result FROM DUAL;
SELECT 200 AS integer_result FROM DUAL;

everything is fine. If I try this:

SELECT 200/7 AS integer_result FROM DUAL;
SELECT 200/7 AS integer_result FROM DUAL;

I get

ORA-00933: SQL command not properly ended.

I can fix it by including a slash:

SELECT 200/7 AS integer_result FROM DUAL;
/
SELECT 200/7 AS integer_result FROM DUAL;

Obviously division has confused Oracle, the poor thing. I have never seen this in any other DBMS, but we live to learn.

What is the rule for adding slashes between statements and why?

The question at What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line? has some information, but the accepted answer basically says there’s no difference, which is clearly not right in this case.

Answer 1

It is your environment, not the database. Above commands work as expected.

SQL> SELECT 200/7 AS integer_result FROM DUAL;

INTEGER_RESULT
--------------
    28.5714286

SQL> !cat 1.sql
SELECT 200/7 AS integer_result FROM DUAL;
SELECT 200/7 AS integer_result FROM DUAL;

SQL> @1.sql

INTEGER_RESULT
--------------
    28.5714286


INTEGER_RESULT
--------------
    28.5714286

SQL>

Answer 2

You didn't specify which tool you use, but - that's something that happens in e.g. SQL*Plus when SQL terminator character is set to slash.

SQL> set sqlterminator /
SQL> SELECT 200/7 AS integer_result FROM DUAL;
  2  /
SELECT 200/7 AS integer_result FROM DUAL;
                                        *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>

If that's your case as well, set it to default character - semicolon:

SQL> set sqlterminator ;
SQL> SELECT 200/7 AS integer_result FROM DUAL;

INTEGER_RESULT
--------------
    28.5714286

SQL>

---

As of original question you asked: semicolon terminates one statement, slash terminates PL/SQL block or (again - in SQL*Plus and similar tools) - re-executes previous command.

Semicolon, terminating one statement:

SQL> SELECT 200/7 AS integer_result FROM DUAL;

INTEGER_RESULT
--------------
    28.5714286

In PL/SQL, semicolon terminates statement (e.g. line #2), while slash terminates the whole procedure (line #4):

SQL> begin
  2    null;
  3  end;
  4  /

Without slash, if you keep hitting the ENTER key, you'd get bunch of empty lines until you get bored (or - finally - hit the slash):

SQL> begin
  2    null;
  3  end;
  4
  5
  6

PL/SQL procedure successfully completed.

Re-executing previous statement:

SQL> select sysdate from dual;

SYSDATE
---------
02-MAR-23

SQL> /

SYSDATE
---------
02-MAR-23

SQL>