Note to the visitor: Thank you for stopping by. I see a lot of people trying to find out about forward slash in SQL*Plus. I want to clarify: This post only applies to Oracle SQL. If you are you are querying about a "/" usage in any other database, you are in the wrong place.
Forward Slash ("/") in Oracle SQLThis post is about using forward slash ("/") in
Oracle SQL and
PL/SQL. This gets a lot of developers (new and experienced alike) as it's not always needed and sometimes it shouldn't be used.
Have you ever tried typing "/" (without quotes), as soon as you logged into
SQL*Plus? Try it. You will see something like below.
SQL> /
SP2-0103: Nothing in SQL buffer to run.
SQL>
The above snippet explains it all pretty well. Forward slash ("/" ) is a shortcut for
RUN command in SQL*Plus (like
"go" in SqlServer or mysql). Since there is nothing to run yet, SQL*Plus returned an error message (prefixed with SP2-).
The message in the above example also shows there is a SQL buffer in SQL*Plus. Any SQL sent to DB ends up in this (one statement) buffer. Also, if there is a statement in the buffer, "/" would have simply re-executed it.
When you type a line and press ENTER, SQL*Plus checks if it's one of it's own commands, a SQL statement or just bad text. If it's a SQL (identified by keywords like SELECT etc), it starts storing it in the buffer until it sees a "/" or a semi-colon, at which time it executes it (actually sends it to the server to execute it).
Gotcha with Forward Slash (/)SQL Statements typically end in Semi-colon. Semi-colon is the
Statement Terminator in ANSI standard. But, in Oracle SQL, you can use "/" to run a SQL, instead of semicolon, like shown. (This is how SPANISH record got inserted in my attached example at the bottom of this post).
SQL> SELECT dummy FROM dual
2 /
Dummy
-----
X
But, here is the
gotcha: If the statement itself contained a semi-colon, then "/" would have re-executed it!!!!
SQL> select dummy from dual;
Dummy
-----
X
SQL> /
DUMMY
----------
X
Oops. Imagine this was an INSERT statement instead!!! (And this is what happened in my example script attached. If you notice the "/" before commit, this is what caused FRENCH to be inserted twice!!).
So be careful with your usage of "/". For plain SQL, it's a matter of choice. Typically, this is used in DDL statements and semi-colons in DML statements. Be consistent to avoid surprises.
Remember "/" is just a RUN (or push) command in the tool, not part of Oracle SQL itself. If you run my attached example script in SQLTools, you will get an error on "/".
Always, run your scripts in SQL*Plus to make sure it will run fine in production, as this is a tool of choice for several DBAs.
Forward Slash ("/") in PL/SQLPL/SQL on the other hand is a group of SQL statements with embedded semicolons. In this case, a semicolon alone cannot be used to send to DB. It needs a push with "/"! So, in this case, it acts as a Statement Terminator as well.
SQL> list
1 DECLARE
2 x NUMBER;
3 BEGIN
4 SELECT 10 as num INTO x FROM dual;
5 Dbms_Output.put_line('x = ' || x);
6
7 END;
8
9
10
11
12
13*
SQL>
In the above example, Notice lines 7 - 13 are blank. This is because I pressed ENTER several times. Since SQL*Plus knows this a PL/SQL (declare, begin...end), it waits for me to signal the real end, which is..... a "/". Until I typed "/" on line 13, it didn't come out of the PL/SQL editing mode. "/" also pushed the PL/SQL block to server to execute it.
Even though this has a bunch of lines, this whole text is a single PL/SQL statement. Type list at the SQL*Plus Prompt. You will see the "single" statement listed in full (minus the "/").
SQL*Plus commandsSQL*Plus commands like SET, SHOW, SPOOL etc are typically one liners and should not be ended with semicolon (and don't need "/" either).
SQL> show serveroutput;
serveroutput OFF
SQL>
These commands will be executed locally in the tool and won't be stored in SQL buffer. So, a "/" after a SQL*Plus command will not re-execute it. In fact, it will re-execute the previous SQL Statement in the buffer!!
(Note: A "/" must be the first non-whitespace character (space, tab etc) on a line by itself. It cannot be at the end of a line, like semi-colon can be).
I've also posted a more detailed description of SQL*Plus
here.Notes
- There is a subtle difference: RUN actually lists before it runs; / doesn't!
- This is a local message from SQL*Plus. If this was from Oracle database server, you would see a "ORA" prefix.
Example Script-- Example data for SQL*Plus related blogs
DROP TABLE greetings;
CREATE TABLE greetings (id NUMBER, lang VARCHAR2(10), msg VARCHAR2(30));
INSERT INTO greetings VALUES (1, 'ENGLISH', 'Hello World');
INSERT INTO greetings VALUES (2, 'FRENCH', 'Bonjour le monde');
/
INSERT INTO greetings VALUES (3, 'GERMAN', 'Hallo Welt');
list
/
INSERT INTO greetings VALUES (4, 'SPANISH', 'Hola Mundo')
/
COMMIT;
-- Here are some extra notes about this script:
-- Just Hello world in some languages. Translations are approx. from Google.
-- Even though we are submitting this script to SQL*Plus entirely,
-- it executes one SQL Statement at a time. List command shows this.
-- Forward Slash after FRENCH, reexecutes previous command. so you will see
-- 2 FRENCH records inserted.
-- Forward Slash after GERMAN was, by mistake, thought to run list, but instead it
-- it ran the previous SQL statement, thus we have 2 GERMANs!!
-- Forward Slash after SPANISH inserts only once - because it's missing semi-colon (;)
-- and / substitutes for it!!
-- Commit is required in Oracle SQL. By default, Autocommit is not turned on in SQL*Plus
-- To see/set default use SHOW/SET AUTOCOMMIT
Further Reading
- See my Take 2 on this topic here.
- If you are looking for any relationship between forward slash and commits (there is none), please see my post here.
- If you are looking to see if Forward Slash somehow affects DDLs, please see here.
- Please see here for a more detailed description of SQL*Plus in general.
ReferencesSQL BNF Grammars
- http://savage.net.au/SQL/sql-92.bnf.html
- http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt
- http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
