Oracle Magazine - September/October 2010

ASK TOM Code Listing 4: Using SQL*Plus error logging with Oracle9i Database

sqlplus scott/tiger@ora9ir2

Connected to: Oracle9i Enterprise Edition Release 9. 2.0.8.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9. 2.0.8.0 - Production scott%ORA9IR2> set errorlogging on scott%ORA9IR2> selct from dual;

SP2-0734: unknown command beginning "selct fr..." - rest of line ignored.

scott%ORA9IR2> select statement, message 2 from sperrorlog;

STATEMENT MESSAGE ————————— ——————— selct SP2-0734: unknown command beginning from dual; "selct fr..." - rest of line ignored.

that runs SQL*Plus to run a script can easily check to see if any errors have occurred in your session.

(Thanks to Enrique Aviles [ bit.ly/ beXbde] for pointing it out, and thanks to Arup Nanda for writing it up [see “SQL*Plus Error Logging” at oracle.com/technology/ pub/articles/oracle-database-11g-top- features/ 11g-misc.html].)

Note that you need Oracle Database 11 g SQL*Plus, not just Oracle Database 11 g with an old version of SQL*Plus connected to it. This is a feature of SQL*Plus.

On the flip side, though, this means that the new SQL*Plus error logging is available for older database releases! You can connect to Oracle9i Database with Oracle Database 11 g SQL*Plus by using the code in Listing 4. W

plan, right card = right plan,” where card is cardinality).

Now, when I run the query and ask Oracle Database, “Which plan did you use?” I get to see “reality”—not an explain plan (explain plans do not always reflect reality)—and Listing 2 shows reality.

The reality in Listing 2 is not any different from the explain plan in Listing 1 at this point. Reality says, “I came up with a plan based on an estimated cardinality of 8,168 rows.” However, because the database actually ran the query in Listing 2, it has learned from its mistake. I run the query again in Listing 3, and you can see what the database learned.

The database hard-parsed that query, because the actual observed row counts were so far removed from what it guessed— it did not reuse the plan. In Listing 3, you can see that the estimated cardinality in the plan is different—the row count ( 6) is much lower (than 8,168). This can have a dramatic effect on the overall query plan used by the optimizer. A new thing about an old tool. The next new thing I learned is about a new feature in the venerable old SQL*Plus tool. The learning began when I was asked on Ask Tom about trapping the SP2 errors in SQL*Plus, those you get when you have a bad SQL*Plus command such as this one:

SQL> selct from dual;

SP2-0734: unknown command beginning "selct fr..." - rest of line ignored.

In this case, neither of the built-in SQL*Plus error handling capabilities— OSERROR or SQLERROR—will help you. The error is not an OS error such as “unable to open spool file,” and it is not a SQL error, because “selct” is not SQL, so the statement never got to the SQL layer. That SP2 error is uncatchable, and I wrote as much in my answer.

As I mentioned earlier, things change. So although my answer is dead-on correct for Oracle Database 10g Release 2 and earlier releases, it is not technically true in Oracle Database 11 g and above.

SQL*Plus in Oracle Database 11 g added an error logging facility. So a session can now issue

SQL> set errorlogging on

and have any SQL, OS, or SP2 errors logged into a logging table, much as with DML error logging. Additionally, you can have your errors tagged with an identifier, making it easy to find your error records. So you can now check (using SQL) at various times to see if you’ve hit an error, and your program

Tom Kyte is a database
evangelist in Oracle’s
Server Technologies
division and has worked
for Oracle since 1993. He
is the author of Expert
Oracle Database Architecture, Second Edition
(Apress, 2010) and Effective Oracle by Design
(Oracle Press, 2003), among other books.