Externalizing internal errors for external tables

Question:
I'm working with external tables in PL/SQL, and I want to handle exceptions dealing with all the things that can go wrong with external tables: file missing, permissions, etc. However, the top-level error for all external table errors is: "ORA-29913: error in executing ODCIEXTTABLEOPEN callout". What I really need to react to is one of the other errors, which is actually a KUP error, such as: "KUP-04040: file pmm_po_activity.dat in PMM_TABLE_DIR not found". Any solution to this?
Answer:
I have very little experience with external tables, though they look very handy and interesting. This question will focus on how you can (and sometimes need to) extract information from the Oracle error stack — because that is your challenge: the specific "KUP" error is several layers down in the stack of errors, and the SQLCODE only returns the error code at the top of the stack.
In this answer, we will explore the error situation with external tables, and then I will provide you with a utility that will do the parsing work necessary to extract the KUP error from the stack.
In terms of external tables, let's take a look at an example (a simplified version taken from Oracle documentation — http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#i1009331) that will raise errors in a variety of ways.
To create an external table (a file on the operating system that can be manipulated by SQL statements as if it were a relational table), I will first define a database directory (you will need the CREATE ANY DIRECTORY privilege to do this) so that I can specify the location of the directory:
CREATE DIRECTORY TEMP AS 'c:\temp'
/
Next, I will create the emp_load external table, specifying TEMP as the default directory (I am not going to explain the syntax and basic functionality of external tables; please visit the Oracle documentation to get up to speed on this feature):
CREATE TABLE emp_load
(employee_number CHAR(5),
employee_last_name CHAR(20))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TEMP
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
employee_last_name CHAR(18)
)
)
LOCATION ('info.dat')
)
/
And if the c:\temp\info.dat contains this data:
12steven
34veva
56eli
78chris
99sean
then the following query in SQL*Plus will yield the results shown:
SQL> SELECT * FROM emp_load
2 /

EMPLO EMPLOYEE_LAST_NAME
----- --------------------
12 steven
34 veva
56 eli
78 chris
99 sean
As I said, a very interesting feature! Yet if I delete the info.dat file and run the same query, I then get an error:
SQL> SELECT * FROM emp_load
2 /
SELECT * FROM emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file info.dat in TEMP not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
And if I restore the file but change the table definition as follows (I added a third column in the table column list, but did not also add it to the list of fields later in the statement):
CREATE TABLE emp_load
(employee_number CHAR(5),
employee_last_name CHAR(20),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TEMP
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
employee_last_name CHAR(18)
)
)
LOCATION ('info.dat')
)
/
then I see a different error message in SQL*Plus:
SQL> SELECT * FROM emp_load
2 /
SELECT * FROM emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04043: table column not found in external source: EMPLOYEE_HIRE_DATE
ORA-06512: at "SYS.ORACLE_LOADER", line 19
That's a whole lot of text, and as you can see the KUP error is several lines down in the message.
Now let's move this query inside a PL/SQL block and analyze the error information more closely:
SQL> DECLARE
2 x INTEGER;
3 BEGIN
4 SELECT COUNT ( * )
5 INTO x
6 FROM emp_load;
7 EXCEPTION
8 WHEN OTHERS
9 THEN
10 DBMS_OUTPUT.put_line ( '---------------------' );
11 DBMS_OUTPUT.put_line ( SQLCODE );
12 DBMS_OUTPUT.put_line ( '---------------------' );
13 DBMS_OUTPUT.put_line ( DBMS_UTILITY.format_error_stack );
14 DBMS_OUTPUT.put_line ( '---------------------' );
15 DBMS_OUTPUT.put_line ( DBMS_UTILITY.format_error_backtrace );
16 END;
17 /
In other words, display the current error code, error stack (Oracle recommends you use the DBMS_UTILITY.format_error_stack function rather than SQLERRM to avoid truncation of the error message) and the error backtrace (available in Oracle Databse 10g only). After running this block, I then see the following:
---------------------
-29913
---------------------
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file info.dat in TEMP not found

---------------------
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 4
Conclusions to draw from this information:
• Oracle returns a SQLCODE of -29913 for at least some of the external table errors. I also did some searching in the online documentation system (http://tahiti.oracle.com) and found some other errors, such as:
• ORA-30655: cannot select FOR UPDATE from external organized table
• Cause: A select for update on an external table was attempted.
• Action: Don't do it!
and even more in the ORA-30656 to ORA-30659 range. For such errors, I will assume that you can use your standard error handling logic, since the SQLCODE corresponds directly to the external table error. This answer focuses instead on how to deal with the "embedded" KUP errors: those that are further down the error stack.
• The specific external table error that we will deal with is of the form KUP-99999, where 99999 is between 550 and 11012; you can see the full list of KUP errors at http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/kupus.htm
Well, I could stop there and let you write the code to search through an error stack and retrieve the KUP error. However, I just love to write PL/SQL code generally and useful PL/SQL utility packages in particular. So I have created a package named exttab to help you extract error information from the Oracle error message when working with external tables.
You can download this package (and associated test code) from:
http://oracle.com/technology/pub/columns/plsql/files/exttab.zip
The specification of this package is as follows:
CREATE OR REPLACE PACKAGE exttab
IS
PROCEDURE set_custom_error_message ( text_in IN VARCHAR2 );

FUNCTION ERROR_CODE ( sql_error_message_in IN VARCHAR2 )
RETURN PLS_INTEGER;

FUNCTION error_message (
sql_error_message_in IN VARCHAR2
, text_only_in IN BOOLEAN DEFAULT TRUE
)
RETURN VARCHAR2;

FUNCTION ERROR_CODE
RETURN PLS_INTEGER;

FUNCTION error_message ( text_only_in IN BOOLEAN DEFAULT TRUE )
RETURN VARCHAR2;
END exttab;
and here is an example of using this package:
DECLARE
x INTEGER;
BEGIN
SELECT COUNT ( * )
INTO x
FROM emp_load;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_kup_code PLS_INTEGER;
l_kup_message VARCHAR2 ( 32767 );
BEGIN
l_kup_code := exttab.ERROR_CODE;

IF l_kup_code = -4040
THEN
DBMS_OUTPUT.put_line
( 'Error: file not found - actual error message:' );
DBMS_OUTPUT.put_line ( exttab.error_message );
ELSE
RAISE;
END IF;
END;
END;
/