SQL Tools Make Fast Work
By Jonathan Gennick
Different SQL tools run code, return results, and offer helpful differences.
The purpose of any interactive SQL tool is to let you enter SQL statements and get results back. The term SQL statements includes PL/SQL blocks and now, in Oracle Database 10g Release 2, XML queries. SQL*Plus has long been the tool of choice for executing ad hoc SQL statements, but today you have more choices, in the form ofiSQL*Plus and Oracle Application Express ( formerly Oracle HTML DB) 2.0's SQL Workshop. This article looks at Oracle's interactive SQL tools, highlights their strong points, notes their differences, and suggests what each is best suited for.
SQL*Plus is a part of all Oracle Database installations. You can also install SQL*Plus without a database installation by installing the Oracle Database 10g Instant Client—Basic or Basic Lite package—and the SQL*Plus package.
In SQL*Plus, you can run SQL, PL/SQL, and SQL*Plus statements. In Oracle Database 10g Release 2, you can also use the new XQUERY command to run ad hoc XML queries from SQL*Plus.
Starting with statements. To execute a SQL statement from SQL*Plus, you simply type it in. You might type short statements all on one line, but you'll want to break longer statements into multiple lines for readability. SQL*Plus buffers each line you type, until you do one of two things:
In response to either of these events, SQL*Plus will send your statement to the database for execution and then display the results. The procedure for entering and executing a PL/SQL block is much the same, except that you must use the forward-slash method to terminate a PL/SQL block.
SQL*Plus implements some handy commands to let you write individual SQL statements to and read them from a text file. To save the most recent statement (or block) you've entered into a file named your_file.sql, you can issue the following command:
SAVE your_file REPLACE
Use the keyword REPLACE to ensure that your statement gets written even if a file named your_file.sql already exists.
To retrieve a previously written statement, use the GET command:
The GET command reads a single statement from your_file.sql into the buffer (and you'll get an error message if there is more than one statement). You can then execute the statement in the buffer by typing a forward-slash on a line by itself and pressing ENTER. For example,
SQL> GET your_file 1 SELECT sysdate 2 FROM dual SQL>/ SYSDATE ----------- 13-Oct-2005
When using SQL*Plus, however, you're likely to want to create, get (for editing), and save scripts consisting of many commands and statements, and to create those scripts, it's more practical to use a text editor such as vi or Windows Notepad.
Note that in Microsoft Windows, SQL*Plus provides standard menu commands that allow you to open, save, and run files as well as define and invoke an editor such as Windows Notepad.
Running scripts. One of the most crucial uses of SQL*Plus is to run scripts. Oracle's database creation scripts all run under SQL*Plus. Database administrators depend on SQL*Plus to run scripts to create schemas, to extract data, to gather database statistics, and to perform almost any database administration task you can imagine.
One way to run a SQL*Plus script is to embed it within a UNIX shell script. The following bash script returns the number of objects owned by the user running the script:
#!/bin/bash sqlplus -s / << EOF COLUMN obj_count NEW_VALUE object_count SELECT COUNT(*) obj_count FROM user_objects; EXIT object_count EOF let "objcount = $?" echo The user owns $objcount objects.
And here's what executing this script from an operating system prompt looks like:
jonathan@home> ./ObjectCount.sh OBJ_COUNT ---------- 3 The user owns 3 objects.
The ./ sequence you see in this example is what you use to indicate to the bash shell that you want to run a script in the current directory. The dot (.) represents the current directory, the forward-slash is the separator, and ObjectCount.sh is the script to execute.
One hint about running scripts: It's best to avoid embedding passwords, for obvious security reasons. To that end, you can take advantage of operating system authentication. The preceding script is designed to run from a UNIX account that has been linked to an Oracle login.
Another way to run SQL*Plus scripts is via the @ command, which causes SQL*Plus to take its input from a file. In this particular case, no shell script is involved—only a text file with SQL*Plus commands, SQL statements, and PL/SQL blocks. The @ command can run a file from your hard drive or from the internet.
Note that the @ command is a SQL*Plus command, not a shell command, so you must execute it from the SQL*Plus prompt. Take, for example, http://gennick.com/msg.sql, which contains the following code:
SET ECHO OFF SET PAGESIZE 0 SET DEFINE ON SET VERIFY OFF SET WRAP ON SET DEFINE ON SELECT 'Thank you for reading' FROM DUAL WHERE '&1'= '2' UNION SELECT 'Brighten the corner where you are' FROM DUAL WHERE '&1'<> '2'; SET PAGESIZE 24 SET VERIFY ON UNDEFINE 1
You can execute the script as follows:
SQL> @http://gennick.com/msg 1 Brighten the corner where you are SQL> @http://gennick.com/msg 2 Thank you for reading
As you can see, the output depends on the parameter you pass to the script. The script uses &1 to reference the first command-line parameter and to determine which of two possible SQL statements should succeed in returning data.
Easy connection identifiers. One of my favorite new features in SQL*Plus for Oracle Database 10g is the easy connection identifier syntax, providing a convenient way to specify connection details from SQL*Plus. If you have ever needed to stop and create a net service name (such as in tnsnames.ora), you'll understand why I like easy connection identifiers so much. For example,
sqlplus /nolog connect username/password@ //home.gennick.com:1521/ prod10r2.gennick.com
The //home.gennick.com syntax specifies the server hosting my target database instance, 1521 is the port number the listener on that server is monitoring for inbound connection requests, and prod10r2.gennick.com is the name of the database service to which I'm connecting. Once you get the hang of the syntax, you can quickly and easily connect to any TCP/IP-connected database.
XML queries. Oracle Database 10g Release 2 introduces native support for XML queries, and SQL*Plus is right there with support for developing those queries. This support is in the form of the new XQUERY command, which lets you execute ad hoc XML queries from the SQL*Plus prompt.
Entering an XML query via the XQUERY command is very much like entering a PL/SQL block. SQL*Plus parses the keyword XQUERY and then buffers everything else you type as an XML query to be sent to the database. Signify that you're done entering your XML query by typing a forward-slash as the only character on a line.
Read more about SQL*Plus' new XML support in the SQL*Plus User's Guide and Reference.
Oracle9i Database introduced a version of SQL*Plus you can access from your Web browser. CallediSQL*Plus, it's a three-tier application in which the work is done on your behalf by an application service running on the database server. Like SQL*Plus,iSQL*Plus can be used to execute SQL*Plus commands, SQL statements, PL/SQL blocks, and XML queries.
One benefit ofiSQL*Plus is that you can provision SQL*Plus access to a group of users without having to install client software on each user's local PC. Another benefit of usingiSQL*Plus is Unicode support.iSQL*Plus runs in a browser, and most browsers support Unicode, so the ability to display multibyte Unicode characters is inherent iniSQL*Plus.
Figure 1 shows theiSQL*Plus Workspace page in a browser just after querying for a list of tables owned by the currently logged-in user. Type any combination of SQL*Plus commands, SQL statements, PL/SQL blocks, and now XML queries (using the XQUERY command is an option) into the text box; click the Execute button; and your results appear in the lower portion of the window.
The yellow callouts in Figure 1 highlight someiSQL*Plus features you'll want to be aware of. The History (1) tab lets you go back and access scripts you've executed during a session. You'll see a list of the commands and scripts you've already executed, and you can click on one to reexecute it. History is a nice feature iniSQL*Plus that you don't always get from command-line SQL*Plus (depending on the underlying platform).
The Load Script (2) button lets you load a script from a file on your hard drive, and after you've debugged that script, you can save it again by using Save Script (3) . The Preferences (4) button gives you access to several settings that control script execution and output. (You can control the same settings via the SQL*Plus SET commands, or, in the Windows version of SQL*Plus, from the Options->Environment menu option.)
iSQL*Plus gives you no access whatsoever to the server on which it runs, so you cannot use commands such as SAVE , GET , and SPOOL . You also cannot run scripts from your local file system by using the @ command, something you can do by using SQL*Plus. However, you can run a local script easily enough, by clicking on the Load Script button, loading your script into the workspace, and then clicking on the Execute button.
For the most part,iSQL*Plus gives you compatibility with SQL*Plus that is as complete as it can be in a browser.
Oracle Application Express's SQL Workshop
The enhanced SQL Workshop in Oracle Application Express 2.0 provides interactive SQL execution, GUI-based query building (in a browser!), script editing and execution, a very nice object browser, the easiest comma-separated-values (CSV) importing I've ever seen, and more.
Figure 2 shows how easy it is to see the execution plan for a query with SQL Workshop. You can also create an execution plan in SQL*Plus, but you must include code to create the plan and to query the table containing the plan. The SQL Workshop approach is much simpler. Just type your query into the text box and click on the Explain link below. The execution plan is retrieved and very nicely formatted for you to look at. Some items in the plan are hyperlinked, allowing you to drill down for even more detail. If you're interested, for example, in the structure of the DEMO_ORDERS table, just click on that object link in the plan.
To see the results of the query in Figure 2, you would click on the Results link to switch views, followed by the Run button at the top right. You execute a PL/SQL block from SQL Workshop in the same manner as with a SQL statement: Type (or paste) the block into the text box, and click on the Run button.
To save a query under a name, with a description of what it does, click on the Save button, at the top right of Figure 2. To see a list of such saved queries, click on the Saved SQL link under the text box. You'll be presented with a list of previously saved queries, including their descriptions and SQL text.
Click on the name of any saved query, and it will be placed within the text box for you to edit or run. Can't find the query you're looking for, because it's buried in a long list? Not to worry, you can easily search the names and statements of saved queries to home in on the one you want, by clicking on the Saved SQL button and then entering some search text into the Find text box.
SQL Workshop does not support a "convenience feature" such as SQL*Plus' XQUERY command for executing XML queries. However, you can easily execute XML queries by invoking the XMLTABLE and XMLQUERY functions from within SELECT statements. This approach fits with SQL Workshop's mission of helping you develop and debug SQL statements that you can then plug in to your applications.
SQL Workshop is not SQL*Plus—don't try to use SQL Workshop to execute SQL*Plus commands such as SPOOL or FORMAT .
Figure 3 shows the drag-and-drop version of a query very similar to the one you saw in Figure 2. To create this query, you do the following:
As you click on each table name on the left, a representation of that table, with a list of its columns, appears in the query pane. Once you have two tables in the pane, you can specify the join criteria:
As you click on the second join box, a line connects the corresponding columns in the two tables, indicating the join. That will open up a join-type menu from which you can choose the type of join. The join in Figure 3 is an inner join.
With the tables and join in place, check off the columns that should appear in the query's result set. Check them off in the order in which you want them to appear. Don't worry if you get the order wrong, though, because you can easily adjust the column order by clicking on the up/down arrows to the left of each column name in the list at the bottom of the screen.
Last, specify grouping and summarizing. Each column has a Group By check box; check that box for both the ORDER_ID and ORDER_TOTAL columns. There's also a drop-down list called Function. Select the SUM function for QUANTITY and UNIT_PRICE . Now you're ready to execute the query, which you can do by clicking on the Run button.
SQL Workshop Scripts
You can create and execute scripts from SQL Workshop too. Figure 4 shows the script editor open to a script that creates backup copies of the DEMO_ORDERS and DEMO_ORDER_ITEMS tables. The script editor is nicely implemented, and if you look closely at Figure 4, you'll see a thin bar extending vertically from the cursor position, making it very easy to consistently line up indented lines in a long script. In addition to using the editor, you can upload scripts from files on your hard drive. To upload a script, click on the UPLOAD button on the initial script editor screen.
Scripts in SQL Workshop can be composed only of SQL statements, PL/SQL blocks, and comments. (Remember, you execute XML queries via function calls in SELECT statements.) If you try to run a SQL*Plus script, SQL Workshop will warn you that you are trying to run a script containing commands it doesn't recognize. It'll even list those commands for you, so you can decide whether or not to run the script anyway. This is a very nice touch, because you can sometimes get away with running a SQL*Plus script in SQL Workshop, if the SQL*Plus-specific commands aren't critical to the running of the script.
One very nice feature of SQL Workshop is that it shows you script results as they accrue, while a script is running. Thus, if a script is executing a series of long-running SQL statements, you can see the results from the earlier statements without having to wait for the script to complete. You can also choose between detailed and summary results. Summary results show you enough to indicate whether each statement in a script has succeeded or failed. Detailed results show all output from each statement, including the results from SELECT statements.
Unlike SQL*Plus, which does allow you to spool script output to a file, SQL Workbench maintains script results in the database, where you can easily access them from any browser. And script results are saved until you explicitly delete them.
If you've ever spent time getting to know an unfamiliar schema, querying data dictionary views for table names, index and constraint definitions, and the like, you'll find much to like about SQL Workshop's object browser. My favorite object browser feature is the ability to view diagrams showing groups of related tables. You can generate Figure 5 by clicking on the DEMO_ORDERS table and then the Model link at the top of the output pane. SQL Workshop follows the foreign key constraints and generates the diagram showing DEMO_ORDERS in position with other related tables.
As you can see in Figure 5, the object browser lets you look at table data, indexes, constraints, grants, statistics, and other information about the currently selected table. You can also modify tables, rename tables, and perform other useful tasks such as copying tables and creating lookup tables. The list of what you can look at and the list of what you can do both change to conform to the type of object you are currently viewing.
SQL Workshop offers a useful set of utilities and canned reports, including one of the neatest utilities I've seen for importing comma-delimited data. When using SQL*Loader or an external table to load such data, you must first examine your data and then create a control file describing it. SQL Workshop makes things much easier. First you pass it a comma-delimited file. Then SQL Workshop presents you with the page shown in Figure 6. All you have to do then is look at the example data and give each column a name and a datatype. In Figure 6, I've filled in that information for the first four columns.
A flowchart on the left side of the import and export pages keeps you apprised of where you are in any given import/export process. Figure 6 shows the import of a delimited text file. The Table Properties box is highlighted in dark gray, indicating the second step of a three-step process. Clicking on Next will take you to a page where you can specify a primary key for the table about to be created, and from that page, you would click on the Import Data button to begin the import process.
SQL Workshop lets you export comma-delimited data as easily as you can import it. You can also import and export data in XML format.
Which Tool to Use?
With all these tools available, how do you choose between them? To some extent, it depends on which you find most comfortable to use.
SQL*Plus is the tool of choice for database administration scripts for a number of reasons. It is simple to use, always available, and can be invoked from shell scripts. In addition, it has other features for automating routine, administrative tasks, such as the ability to spool script output to a text file and the ability to prompt for input.
iSQL*Plus can be used for just about anything you would do in SQL*Plus interactively (SQL*Plus is a better choice for batch processing) but requires no client-side software installation.
Oracle Application Express 2.0's SQL Workshop has a lot to offer in the way of convenience features. It's an easy tool for developers to use when learning about an unfamiliar schema, and it has probably the most convenient explain-plan feature of the three tools. If you haven't tried it, the new SQL Workshop is well worth a look.
Jonathan Gennick (Jonathan@Gennick.com) is an experienced Oracle professional and member of the Oak Table Network. He wrote the best-selling SQL Pocket Guide and the Oracle SQL*Plus Pocket Reference, both from O'Reilly Media.