External Table error Capture!

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
Hi Expert,

When i'm selecting data from external table getting following error , i want to capture all the error into a log for the error visibility.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-05002: There are no matching files for any file specification in the LOCATION clause.

--I'm using below pkg for creating message for user

o_error_message := SQL_LIB.CREATE_MSG ('PACKAGE_ERROR',
                                             'ERROR:'|| L_mark || '|' || sqlerrm,
                                              L_program,
                                              to_char (sqlcode));
      ROLLBACK;
--Here the pkg only print the ORA -Error , Here i'm looking for both "ORA" and "KUP" error.

Open in new window


But now only 1st line "' ORA-29913: error in executing ODCIEXTTABLEOPEN callout"  got printed in output. rest of lines are skipped.

Please share the solution for this.

Thanks in Advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Commented:
Dear,

Please show with more codes.

Oracle does fail when it tries to open one OS file.
Senior Oracle DBA
Commented:
SQLERRM only gives you the current error, not the full stack of errors.  Look at DBMS_UTILITY.FORMAT_ERROR_STACK.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial