Re: Tracing All DML Errors in Oracle 9i

basis_consultant_at_hotmail.com wrote:
>
> Our Oracle 9i (On AIX) database is accessed through Websphere v5. The
> logs
> from Webpshere indicate a bunch of Oracle errors, mainly in insert
> statements (Which generally work when used with OEM or Toad).
However,
> the
> Oracle error number is not mentioned in the Websphere logs; There is
a
> generic 'DataBase Error' type of meesage. The Oracle alert log and
> trace
> files give no indication of any errors.
>
> Is there a way in which I can get Oracle to output each of the errors
> from DML statements; I do not need extensive logs, just a statement
> such
> as Jan. 6, 2005, 1PM, ORA-0001 Unique Constraint Violated, User Y,
> 'INSERT INTO xxx Values zzzz', etc..
>
> I do not think that a statement such as setting
> event='1401 trace name errorstack, level 4' (1401 is an example)
> will help, as I do not know the exact Oracle error numbers.
> Thanks,
> DF

How about wrap the DMLs in PL/SQL code, using either anonymous blocks or (preferably) stored procedures/packages. In PL/SQL, you can set up exception handlers where you can catch the error stack and write it to a file or table.

I think you can also set up DML triggers. It may not perform as well.

It's hard to believe Websphere doesn't have an option to catch the Oracle error numbers.

Yong Huang
yong321ATyahoo.com Received on Fri Jan 07 2005 - 08:40:38 CST