When to use a semicolon or a slash in Oracle SQL

When to use a semicolon or a slash in Oracle SQL? It’s difficult to know when to use a semicolon and when to use a slash in Oracle script. Oracle will throw an error if the semicolon and slash are misplaced. Knowing when to use a semicolon and when to use a slash is absolutely essential. The semicolon terminates the current statement, executes it, and saves it to the sql buffer. The slash terminates the current statement and executes the sql statements from the sql buffer. The semicolon adds the current and previous statement in the sql buffer. The slash removes the previous statement from the sql buffer and inserts the current statement.

The semicolon and slash characters are used to terminate the current Oracle SQL command and execute it in the database. There is a significant difference between a semicolon and a slash. Though they appear to be the same, they execute Oracle SQL in different ways. In most cases, deciding when to use a semicolon and when to use a slash in an Oracle script is tricky.

The semicolon marks the end of the current statement, which executes it and saves it to the sql buffer. The sql buffer remains uncleared. Along with the previous statement, the current statement will be added to the sql buffer. The slash terminates the current statement and executes the sql statements from the sql buffer. The slash clears all previous statements from the sql buffer, adds the current statement to the sql buffer, and executes the current sql statement from the sql buffer.

Semicolon ( ; )

The semicolon ; indicates the completion of a SQL query statement. The semicolon is often used in oracle sql statements to indicate the end of the statement and the start of the sql query execution. There will be no flushing of the sql buffer. The current sql query statement is ended, executed, and added to the sql buffer alongside the previous sql query statement. The previous and current sql statements will be performed from the sql buffer when you rerun the sql query from the sql buffer.

Slash ( / )

The slash / denotes the end of the sql query statement. The slash will flush the sql buffer and insert the current sql query statement into it. When you restart the sql query from the sql buffer, the current sql query statement is executed beginning with the last slash.

The slash / is used to execute the SQL query that is currently in the “buffer.” The slash will execute the sql statement from the sql buffer of the Oracle database. The buffer may include many sql statements separated by semicolons. The slash / will execute all SQL queries presently contained in the current buffer.

Explanation 1

There are two new examples here. The first example will create two tables, each separated by a semicolon. In the second example, two Oracle type objects are created, separated by a slash. This example demonstrates that you cannot put a slash between two create statements arbitrarily.

Example 1

create table emp1 (id integer);
create table emp2 (id integer);

Output

Table EMP1 created.

Table EMP2 created.

In the first example, The first Oracle table cannot be used in while creating the second Oracle table. The second Oracle create table is not related to the first statement. The second Oracle create table statement has nothing to do with the first. As a result, a semicolon is used between two Oracle create table scripts. The semicolon can be used to separate the two distinct independent sql statements.

Example 2

create type type_student;
/
create type type_course;

Output

Type TYPE_STUDENT compiled

Type TYPE_COURSE compiled

Two Oracle types are created in the second example. An oracle type object can be referenced by another oracle type object. When Oracle identifies an item that may be used in another Oracle object, it expects the first object to be created and retained. As a result, the sql buffer will need to be flushed. The slash character is used to remove all previous SQL statements and run the current SQL query from the current buffer.

Explanation 2

There are two PL/SQL blocks, one with and one without a slash. The PL/SQL blocks in the first example are run and flushed simultaneously. The PL/SQL blocks in the second example are separated by a slash. Both PL/SQL blocks are performed sequentially. Both will be run in their own Oracle sql buffer.

Example1

begin
DBMS_OUTPUT.put_line ('empid : 1');
end;

begin
DBMS_OUTPUT.put_line ('empid : 2');
end;

Output

empid : 1
empid : 2

PL/SQL procedure successfully completed.

Example2

begin
DBMS_OUTPUT.put_line ('empid : 1');
end;
/
begin
DBMS_OUTPUT.put_line ('empid : 2');
end;

Output

empid : 1

PL/SQL procedure successfully completed.

empid : 2

PL/SQL procedure successfully completed.

Explanation 3

The semicolon ; terminates the current statement, runs it, and saves it in the sql buffer. The slash terminates the current statement and executes the sql statements from the sql buffer. Previous sql statements stored in the sql buffer will not be flushed. The current sql statement is executed and stored in the sql buffer. When you use the slash to rerun the sql statement from the sql buffer, the previous and current sql statements are executed.

Explanation 4

The slash / clears the current sql buffer, executes the current statement, and saves it in the sql buffer. The slash will execute sql from the sql buffer. The semicolon never clears the sql buffer and always inserts the current statement to the sql buffer together with the previous sql statement. You can run the sql stored in the current buffer at any time by using the slash. The slash will execute the sql statement from the most recent slash command, which may include several sql statements separated by semicolons.