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 querying about a “/” usage in any other database, you are in the wrong place.
Forward Slash (“/”) in Oracle SQL
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/SQL
PL/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 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.
- 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 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
- 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.
SQL BNF Grammars