Oracle: Forward Slash in SQL*Plus – Take 2

Take 2?
I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the traffic. Believe it or not, I still get a lot of traffic to this blog site solely based on this one little topic!! So, I thought I would spend a little more time on this.

WordPress has a nice feature in the Stats page that shows the Search engine terms that drove the user to my page. This is what some enters in say, Google and clicks Search. Here are some sample terms on Slash:

sqlplus forward slash
sql forward slash
forward slash in sql
oracle forward slash
oracle sql forward slash
slash in sql
sql slash
…..
commit vs forward slash
oracle pl/sql semi-colon or forward slash
oracle slash before commit
oracle sql commit slash
forward slash in ddl file

and so many variations of these. One thing that caught my attention (in bold) was the confusion user(s) had with commit and Forward slash. They really don’t have anything in common, but I guess Oracle behavior in different scenarios could lead one to that. So, I thought of explaining these one more time.

Then Vendor said: Let there be a Database and a (client) Tool

Let’s start from the beginning. A database system is typically run on a remote server. To work with it, the vendors – like Oracle, Sybase etc- provide a client tool. Oracle has always come with SQL*Plus as a client tool. This is a simple command line tool, yet powerful. People use it for both interactive and batch use. Oracle also came up with “better” tools eventually, like iSQL*Plus, SQL Developer etc. Similarly, Sybase has Interactive SQL. Apart from these tools, there are plenty of tools available from outside vendors. Some prominent ones for Oracle are Toad, SQLTools, PLSQL Developer etc. The modern tools are typically GUI based and provide plenty of options for interactive usage. Irrespective of that, SQL*Plus is still going strong. Several DBAs prefer this tool to flashy new GUI tools, so whichever tool you use, you may want to make sure your SQL will run SQL*Plus.

Why those Punctuation?
In a GUI tool, you can type up a SQL and click on a button (or press a key), the tool will get the SQL executed (send it to Oracle server, get the results etc). If there are multiple SQLs, you just highlight one and click and go. Now, SQL*Plus is a command line tool. It’s text based and often used in both interactive and batch modes. You couldn’t really click a button or press a key to execute SQLs. Especially, if it’s running SQLs from a file. Then you need a marker to show the end of each SQL, right? That, my friend, is the semi-colon – the statement terminator. Sort of like the period at the end of an English statement. Semi-colon is mentioned as the statement terminator in the SQL Standards books also.

Unfortunately, SQL Standards came much later. Database vendors already came up with their way of telling their tool to execute the SQLs. For e.g., client tools for Sybase and Microsoft SQL Server use go command. That makes sense. Type in a SQL. Key in go and it goes! (SQL is sent to server to be executed etc). Oracle (SQL*Plus) had a similar command – RUN!! So, if you are running SQLs in a file, you would have a SQL followed by RUN and then the next SQL followed by RUN and so on. This is good. But then Oracle decided to give us the short cuts. Like Forward Slash (“/”) for RUN1. (Those early days, every byte counted. Why type 3 chars for RUN and why store so many of them in a file? Slash will do it for cheaper price!!).


1. Actually RUN = Load & Run and the shortcut for it is “R” while “/” is to just “Run” what’s already loaded

What does Forward Slash mean?

So, there you have it. Forward Slash executes a SQL before it. If it was left like that, things probably would have been simpler. Oracle also decided to support the statement terminator, semi-colon. (Sybase and other databases did not require this earlier). Now, you have a semi-colon that could get the SQL executed and a Slash that could do the same thing. They are not the same, but served the same purpose of indicating end of a SQL statement in SQL*Plus!! This started the trouble, especially when people started mixing them. Imagine what happens when you have a semi-colon and a forward slash?

Then came PL/SQL. Now, PL/SQL is a programming language in Oracle which can have several embedded SQLs, often separated by you guessed it – semi-colon – the statement terminators. Now you can have a bunch of PL/SQL block in a file, how do you distinguish each block? Don’t you need something like a PL/SQL statement terminator? Well, semi-colon is already taken. So, Oracle came up with the ingenious way of reusing a character that already had a purpose – you guessed it, the Forward Slash!!!!! It was meant to be “RUN” the SQL, right? Now, it also has the purpose to terminate PL/SQL. (It could also mean RUN the PL/SQL). That paved the way to all the troubles we face in mixing these up.

Semi-colons and Forward Slashes

That mixture combined with SQL*Plus’s own quirks made it worse. In SQL*Plus, you can type a (SQL like) text and end it with semi-colon, it will pass it on to the database. You type a SQL followed by slash (“/”), it will be sent to the server as well. What happens when you have a semi-colon and slash? It gets executed twice. What happens when you have 2 semicolons? You might get an error on the second semi-colon. This is because of the way SQL*Plus works. As soon as you run a SQL, SQL*Plus stores it away in a buffer. Next time you type “/”, it re-executes what’s already stored in the buffer. Type “/” again, it runs it again. Until you type up a new SQL. You can smell trouble, right? People run into trouble because they don’t understand the nuances or simply because they mistyped.

Remember, “/” is a command. So, it has to be on a line by itself. Semi-colon on the other hand, has to be the last character on a line. If there is anything else after that, even a comment (–) or another semi-colon will cause an error!!

Image 595

Anything after semi-colon generates an error.

Image 596

Double semi-colon does too.

DML, DDL etc
When I mentioned SQL above, I meant any type of SQL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE etc. First 4 deal with data in tables. SELECT is a query. INSERT, UPDATE, DELETE make up the DML. There are whole bunch of SQL statements like CREATE TABLE that actually define the structure of a database object (here TABLE). These are called Database Definition Language (DDL). What we saw above applies to both DMLs and DDLs. You can use semi-colon to end DML statements as well as DDL statements. You can use Forward Slash with DML and DDL statements.

To confuse you a bit, general convention in Oracle world is to use Forward Slash to end DDL statements. This would make sense if you look at the syntax for creating Stored Procedures, Functions etc. These are PL/SQL objects. Remember, I said PL/SQL needs to be ended in Forward Slash? Now that we decided to (have to) use Forward Slash for DDL for Stored programs, why not use it for all DDLs? So, there you have it. This is how we arrived at Semi-colon (“;”) to end DML statements and Forward Slash (“/”) for DDLs.

How did COMMIT enter this discussion about Forward Slash?

Phew! We got through that fine. Now, where does commit come into the picture and why confusion about it? Typically, Oracle has Auto-commit option turned off by default. You will have to issue an explicit COMMIT to commit a transaction. (This helps us to have bunch of related DMLs inside a transaction).

Here again there are some short cuts and quirks in the tool that made it more confusing. If you didn’t add an explicit COMMIT and simply exit SQL*Plus, the tool will issue the COMMIT for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it’s automatically committed and DDLs typically end with Forward Slash (“/”). Now, I can imagine why the user searched for a connection between Commit and Slash. He/She is probably running a script that has “/” at the end. The user probably ran the script and when SQL*Plus exited, it probably committed. Are you seeing the connection now? Really, there is no connection between “/” and COMMIT. They just look to be related because of quirks and twists in the tool and the specs.

What was all the blabbering about?

In summary, Forward slash is like go in SQL Server, but use with caution, as it’s not only the RUN command, but could be indirectly construed as a statement terminator as well. Forward Slash (“/”) does not commit. SQL*Plus may silently COMMIT your transaction when you exit. But, always add COMMIT and ROLLBACK explicitly.

And Remember, all this is specifically applicable to SQL*Plus. But, since it has become a de facto standard for running SQLs from a file, you make sure to follow these standards to avoid surprises.

And that mumbo-jumbo applies only to Oracle SQL or PL/SQL. Forward slash doesn’t have this type of special meaning in other databases!!!!

Advertisements

6 thoughts on “Oracle: Forward Slash in SQL*Plus – Take 2

  1. Thanks, this helps.
    So I have developers that write SQL & PL/SQL using SQLDEVELOPER that includes DML and DDL but does not include any forward slashes at all. Thanks to your explanation, now I see that I need to edit their DDL statements with a forward slash at the end to get them to run in my SQL*Plus scripts. One thing I don’t understand though is some of their DDL scripts run properly in SQL*Plus without a trailing forward slash e.g.:
    DROP TYPE SOME_TYPE;
    DROP TYPE ANOTHER_TYPE;
    What quirky rule applies here?

    1. Hi Joel,

      Thanks for the comment. When it’s a simple DDL like DROP TYPE or even CREATE table a semicolon or a slash would do. I emphasized on OR there. Some people try to put semi-colon and /, thinking DDLs always need a slash.

      The only DDLs that really need a slash are the programs – the PL/SQL programs – Prcedures, Functions, Triggers etc. There you need a semi-colon on the last SQL statement and you need a / to finish it. Just the other day, a senior developer missed the slash in a series of SQL files, thus SQL*Plus mish-mashed them to produce unexpected results.

      Because of this, I see DBAs suggesting to use slash (/) for all DDLs, so it won’t be forgotten when you really need them.

      So, the example you gave would look like,

      DROP TYPE SOME_TYPE
      /
      DROP TYPE ANOTHER_TYPE
      /

      Hope this makes it clear. Again thanks for visiting and the opportunity to explain.

      Thanks
      Sam

      This link hopefully explains it all better.

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