Checking for Errors

How the OLAP DML handles SQL errors

Although the OLAP DML will signal some SQL errors, it does not automatically signal an error when the there is an error in a SQL statement. Instead, the OLAP DML provides support to help you handle errors that are returned.

In your programs, you will need to provide the logic for handling SQL errors. The OLAP DML provides two options, SQLCODE and SQLERRM, whose values reflect the SQLCODE and SQLERRM values set by the Oracle relational database.

SQLCODE option

SQLCODE contains an integer error code number. Your programs should test the value of SQLCODE after every SQL command to make sure that the command executed successfully. You can also test the value of SQLCODE to determine whether you need to break out of a loop.

SQLCODE typically has one of the following values:

Code	Meaning
0 (zero)	The last SQL operation was successful.
100	All requested rows have been fetched.
-1	An error has occurred.
Any value that is neither 0 nor 100	An error has occurred.

SQLERRM option

SQLERRM contains the error message associated with the current error code. It identifies the condition that caused an error to occur.

You can control whether or not this message is sent automatically to the current outfile. When you are debugging a program, you will probably want all SQL error messages sent to the current outfile so that you can see them immediately. However, when your application is in use, you will want to suppress the error messages and handle the error condition in a way more suited to your application.

The SQLMESSAGES option controls whether SQL messages are sent to the current outfile, which is usually the screen.

Procedure: Sending messages to the current outfile

To send SQL messages to the current outfile, issue the following command.

sqlmessages = yes