Oracle Exception Handling

[gulshanr]

I am eager to know, is there any way to handle exceptions raised by any SQL statement. For example.

I am reading a file and inserting into ORACLE Table. I have used INSERT ROWS WITHOUT CLEARING. Now I want to trap the error due to UNIQUE CONSTRAINT Voilation. IN this case I want to go ahead and continue with next record. If there is any other problem like data type mismatch or variable overflow or any error with database (like WHEN OTHERS), I want to terminate the job. Is there any mechanism to handle this programattical situtaion. Any help on this will be highly appreciated.

Thanks,
Gulshan

[vmcburney]

Make sure there is a transformer before your database stage. Set the DB stage array size to 1. In your transformer create a reject link to a flat file or table. In the transformer create a reject output field to hold your error code and error message. Right mouse click in the derivation field and look for output link variables, this should expand out to some values such as the DBMS error code and last error message. Pass these into a routine where the code is parses and appropriate actions, such as error messages or warnings, are taken.