I've posted a few SQL tricks earlier. Most of these were in Oracle and often times, we use SQL*Plus to run these.There are several sophisticated SQL clients available for Windows (Toad is a great tool and at work we use SQL Tools++ and Golden), many even free, but SQL*Plus still remains indispensable. In this post, I will explore this tool.
SQL*Plus is an interesting tool. It's an old, text based, SQL Client that has been in Oracle from the beginning. Whatever it's strengths, user interface isn't one of them. With a command line interface (there is a Windows version (SQLPLusw), but not a major improvement) and quirky commands, it is not exactly user friendly, at least not according to today's GUI standards. Would you believe that SQL*Plus is supposed to be an improvement over what Oracle called "User Friendly Interface"?
Oracle did try to come up with several tools to replace it- a browser version called iSQL*Plus, a java based tool called SQL*Developer, but SQL*Plus survived and may actually outlive those. (See here for the bad news for iSQL*Plus and SQLPlusw in 11g. Among other things, they actually recommend SQL*Plus!!!). See Lauren's blog for the official links about this announcement. Also don't forget to read up this blog setting up SQL*Plus nicely in windows, so you won't miss those little used tools.
Strengths of SQL*Plus
Here are some of the reasons why SQL*Plus is still used in production environments, in spite of it's limitations:
- For one, it is a very stable product. This combined with it's simplicity and small memory footprint, makes it a tool of choice for production.
- It is available on various platforms, and offers an almost identical user interface across different platforms.
- The tool can be run in batch mode; combining this with spooling capability and some commands, we can automate running scripts.
- The tool conforms to the Oracle SQL standards so closely, so it still remains as tool of choice when you want to ensure your SQL will really pass in the Oracle environment.
- And to it's credit, it has added few features over the years. For e.g., you can actually spool it's output to an HTML file by adding a few directives (see my post here).
SQL*Plus as a Client/Server Tool
SQL*Plus, like any other SQL Client, is an interactive Client/Server tool. It has a simple editor (don't expect a fancy editor - the one built in is a rudimentary line editor), which can hold 1 statement (however long) in the buffer. You can open a session in SQL*Plus and start typing Statements at the prompt. After you type a line, you can press enter to go to the next line. SQL*Plus will show a line # to indicate it's ready for the next line. You can keep typing and when you are ready, you can press Enter twice, for it to leave edit mode. At this point, the Statement is in memory ready to be executed. When you are done entering/editing the contents, simply issue a “RUN” command (See below) and the contents (SQL) will be pushed to the DB (server) to execute and return results. When the statement is executed successfully, the statement is then put into the 1-statement buffer for reuse.
Below is my sincere effort to document how I see SQL*Plus is executing commands. It's only an educated guess, so use with caution. SQL*Plus by design is a simple tool that executes one command at a time.
[caption id="attachment_471" align="alignnone" width="984"]
Fig 1. SQL*Plus Design - an educated guess[/caption]As shown there are 3 different types of statements that are processed by the command processor. It uses the keyword and the end of the statement to decide where to send it. If the statement has a semi-colon (";") or a slash ("/") at the end (See below), it is sent to the Database engine. (Try typing anything with a keyword (like SELECT etc) and end it with a semi-colon, you will get a SQL Error, indicating it went to DB Engine). Rest of them are tried as a local tool command.
SQL*Plus as a batch tool
There also seems to be a loop in the command processor, that could read one command at a time from a file and pass it on to the command processor. This combined with few commands to SET and SPOOL makes it a good batch tool. A SQL file is just a combination of different types of statements (SQL, PL/SQL and tools commands).
To run a file with SQL statements you can use START command. The short-cut for this is @. So, run a SQL file, you simply have to type,
@<sqlfile>, where <sqlfile>is any text file with SQL statements.
You can mix tool commands and SQLs in the file. The thing to remember is when we execute a SQL file in SQL*Plus, it processes one statement at a time successively. This is key to understand any problem with your script. You are not passing the SQL file to Oracle, you are giving it to SQL*Plus, which reads and executes one statement at a time depending on the type of statement and terminators used.
Run command and Statement Separator
Oracle uses it's own Run command "(/") and SQL standard for Statement Terminator to execute SQLs.
- Oracle uses Run command as a "statement pusher" to send to DB. This has a short-cut ("/").
- Oracle also allows semicolon (SQL Standard) to terminate SQLs.
- As yet another short-cut, SQL*Plus pushes SQL statements to DB right after a semicolon, so you could use either slash or semicolon there.
- But, with PL/SQL you needed both, as the SQL block will need a semi-colon and to push it to DB, you need a "/".
Potential issues with Run Command
This flexibility in SQL*Plus typically leads to many errors in SQL Scripts. Particularly,
- If you have a PL/SQL in a file and missed the "/", it won't execute the PL/SQL.
- Imagine, you try to drop a table and recreate it with few changes. If the drop command failed, results of successive commands may become unpredictable
- On the other hand, if you added a "/" after a SQL with semicolon, the Statement is run twice!!
- For e.g., you could end up in inserting a record twice which could make some other Select/into to fail downstream.
(Different databases handle this issue differently. tools for SQL Server for e.g., uses "go" as "statement pusher" uniformly for all statements. If you miss a "go" you get an error. MySql handles this slightly differently It uses semicolon for simple SQLs and when it comes to block SQL, it allows us to define a temporary delimiter other than semicolon to mark end of SQL).
See my post here for more details on "/".
How to avoid issues
We can use standards to avoid surprises. I prefer to use semicolons after all SQL (DML and DDL) statements. "/" for PL/SQLs. You may find "/" used for DDLs as well.
If you are planning to use "/" after a SQL statement in a file, I suggest this only for the last SQL statement in the file. This is just a preference for readability.
Few related SQL*Plus gotchas
1. Though both semicolon and "/" could push a SQL statement to Oracle, there is a subtle difference. you can type multiple statements in 1 line separated by semicolon. "/" cannot be used thus.
2. The "/" must be the only character on the line by itself. No other character except spaces may be allowed, not even comments.
3. Sometimes inline comments that start with /* could be construed as "RUN" and may cause syntax errors.
4. Typically, you can include blank lines inside PL/SQL block. But SQL*Plus may frown if you had blank lines inside simple SQL statement. There is SQL*Plus setting (sqlblanklines) to avoid this, but I suggest not to use it to stick to standards.
Reference
As mentioned, SQL*Plus can execute SQL and PL/SQL statements and several of it's own commands. Oracle supports Standard SQL and has several extensions as well. Oracle has very good documentation on this topic and several others. See here for Oracle SQL reference and here for good reference on PL/SQL. See here for a complete reference on SQL*Plus from Oracle. refer to this site for the SQLPlus tool commands.
Links
http://www.williamrobertson.net/documents/sqlplus-setup.html
http://thomas.eibner.dk/oracle/sqlplus/
http://www.adp-gmbh.ch/ora/sqlplus/
http://mti.ugm.ac.id/~adji/courses/resources/CICC/Short%20Cicc/text/or8_text/ch2.htm
http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch2.htm
http://ugweb.cs.ualberta.ca/~c391/manual/chapt4.html
http://laurentschneider.com/wordpress/2006/03/isqlplus-and-sqlplusw-desupport.html
