What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

Question

The following two queries seem to be equivalent when executed in sql*plus:

1.

select * from user_tables;

2.

select * from user_tables
/

Is there any difference between both versions?

Answer 1

There is no difference for a single SQL statement. The '/' character on its own line tells SQL*Plus to execute the command in the buffer. You can use the semicolon at the end of most SQL statements as a shorthand for the '/'. If you want to execute a PL/SQL block or to execute a handful of SQL statements like CREATE TYPE, however, you need to use the '/'

SQL> begin
  2    null;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> create type foo as object (
  2    col1 number,
  3    col2 number );
  4  /

Type created.

Different tools may have slightly different conventions for how you execute multiple SQL and PL/SQL statements in a script so be aware that this is SQL*Plus specific.

Answer 2

And besides what Justin mentioned -- because the / runs whatever's in the buffer, it can also be used after editing the previous command, or even just to run the same command multiple times.

If you call ed in sqlplus, it'll try to write a temp file AFIEDT.BUF in your current directory, and then bring up an editor (vi, notepad, etc.), for you to edit the last command sent. One you're exited the editor, you use / to send the modified buffer.