When do I need to use a semicolon vs a slash in Oracle SQL?

Breaking down semicolons and slashes

Executing SQL statements with a semicolon

The semicolon signifies the end of a SQL command and is universally employed as a statement terminator in Oracle SQL. Other SQL languages like MySQL, PostgreSQL, SQLite also share this convention.

Standard SQL statements (SELECT, INSERT, UPDATE, DELETE) along with other types like Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL) often use semicolon as statement delimiter.

Slash it out for PL/SQL blocks

A forward slash /, all alone on a fresh line, signals Oracle to execute the complete PL/SQL block or the SQL buffer content preceding it. Its usage is required for running CREATE statements and BEGIN...END blocks for PL/SQL.

A neat trick about slashes: If you end a block of code with a semicolon and then follow it up with a slash, you'll re-execute the buffer content, which incidentally is the last code parsed. It may be an unexpected behavior, but understanding its function helps prevent errors and confusion.

Dealing with the dynamic duo: Semicolon and Slash

When scripts with both semicolons and slashes are run from SQL*Plus, they can cause the statement to execute twice. However, in environments like SQL Developer or TOAD, this duplication isn't observed. Here's a simple mantra for various scenarios:

• In anonymous PL/SQL blocks, always include a / following the semicolon that ends the block.
• Always use forward slashes after defining units of work to ensure consistent behavior regardless of the tool used.
• Avoid using a / after a statement that ends with a ; to prevent unwanted re-execution.
• Always consider newline and spacing in your script to avoid confusion or inadvertent slips.

Inserting conditionally and uniquely

Avoid data duplication with conditional SELECT and INSERT statements as follows:

INSERT INTO table_name (column_names)
SELECT column_values
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM table_name WHERE condition
);


This statement uses dual, a special one-row dummy table provided by Oracle, and terminates with a semicolon. It ensures uniqueness by inserting only if a matching record doesn't exist. Say bye-bye to duplicates!

Best practices & common pitfalls

Knowing your tools of the trade

Different interfaces handle semicolons and slashes differently, contributing to the overall behavior of your scripts. A forward slash /, might not be executed in SQL Developer or TOAD the same way as it does in SQL*Plus.

Consistent code construction

To ensure scripts behave similarly across different interfaces, always use the appropriate statement terminators.

Utilizing Oracle documentation

It's recommended to revisit the SQL*Plus Command Reference and Oracle documentation to understand any unexpected behaviors that might be specific to your toolset or related to the use of ; and /.