When to use a semicolon or a slash in Oracle SQL

When to use a semicolon or a slash in Oracle SQL? Determining whether to use a semicolon and when to use a slash in Oracle script is difficult. Oracle will give an error if the semicolon and slash are misplaced. It is critical to understand when and when to use a semicolon and a slash.

The semicolon and slash characters are used to terminate an 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 distinct ways. In most situations, determining whether to use a semicolon and when to use a slash in an Oracle script is tricky.

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.



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.



Slash ( / )

The slash / is used to run whatever SQL query is currently in the “buffer.” The slash will run the sql statement from the oracle database’s sql buffer. The buffer may contain several sql statements, each separated by a semicolon. The slash / will run all of the SQL queries that are currently stored 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, an Oracle table cannot be used in another Oracle table. The second Oracle create table is not dependent by the first. As a consequence, between two Oracle create table scripts, a semicolon is used.

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 discovers an item that may be used in another Oracle object, it anticipates that the first object will be created and retained. As a result, the sql buffer should be flushed. The slash is used to run SQL 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, executes it, and saves it to the sql buffer. The slash terminates the current statement and executes the sql statements from the sql buffer.



Explanation 4

The semicolon ; clears the current sql buffer, executes the current statement, and saves the results in the sql buffer. The slash will execute from the sql buffer. The slash will never clears the sql buffer. Using the slash, you may execute the sql saved in the current buffer at any time.