FORWARD SLASH IN SQL*PLUS

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

This 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 RUNcommand 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

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.

Notes

  1. There is a subtle difference: RUN actually lists before it runs; / doesn’t!
  2. 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

 

FROM HERE

Advertisements

3 comments

  1. SamV · August 12, 2014

    Hi Leon,

    I see that you are reposting my blog posts, verbatim. I don’t mind it, as it only help spread the knowledge wider. A lot of people are making a mistake on this (Forward slash_/

    But, I request that you at least give credit to my page. I would like to see a link to my page somewhere in the article.

    Cheers
    Sam

    • LEON · August 12, 2014

      hey Sam
      Every time I re blog someone’s post, I always add a “from here” link at the end. I am not sure whether you noticed it.

      Anyway , thanks for your great content.

  2. SamV · August 12, 2014

    Hi Leon,

    Thanks for the quick reply. Sorry I didn’t notice the “FROM HERE”. Thanks for referring back. Another blogger used my pages (with his own typos) to gain clicks to his page. That’s why I wanted to make sure. Please feel free to repost any other article you find useful.

    Thanks for complementing on the content. We all learn so much in technology over a time period and it’s always painful when you start with it. I wanted to record it, so it’s useful to someone else starting new. I am still learning to make the blog more useful. Please pass back any comments/suggestions you may have.

    btw, Your blog layout looks nice

    Cheers
    Sam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s