Power to Build

Home » CodeProject » Forward Slash in SQL*Plus

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

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

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.

References

SQL BNF Grammars

  1. http://savage.net.au/SQL/sql-92.bnf.html
  2. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt
  3. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

7 Comments

  1. Justina says:

    Hi there i am kavin, its my first time to
    commenting anywhere, when i read this post i thought i could also
    create comment due to this brilliant post.

  2. […] One of them happens to be Forward slash in (Oracle) SQL. I’ve already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding […]

  3. […] 2? I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the […]

  4. […] my post here for more details on […]

  5. Do you mind if I quote a couple of your posts
    as long as I provide credit and sources back to your blog?

    My blog is in the exact same niche as yours and my users would really
    benefit from some of the information you present here.
    Please let me know if this okay with you. Cheers!

    • SamV says:

      Hi Kulutusluotto Netissa,

      I just saw your message. It was sitting in spam, so I didn’t notice it. I am OK with you quoting the posts. I looked the pikavippii.com. That didn’t sound like the blog you were referring to. Can you please give me the address of your blog?

      Thanks
      Sam

Comments, please?

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

%d bloggers like this: