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
Oracle Exception Handling
Moderators: chulett, rschirm, roy
Hi,
The Oracle Enterprise Stage has an option 'Output Reject Records'. Set this option to 'True' and pass the rejected records to a sequential file through a transformer. In the Transformer set 'Abort after rows' option in the constraint to 1. In the sequential file stage set the 'Cleanup on failure' option to False.
So when you try to insert the same records or records with datatype mismatch the corresponding records will be rejected by the Oracle Enterprise Stage and captured in a sequential file. At the same the time the job will be aborted since you have setup the functionality in the transformer.
HTH
--Rich
Pride comes before a fall
The Oracle Enterprise Stage has an option 'Output Reject Records'. Set this option to 'True' and pass the rejected records to a sequential file through a transformer. In the Transformer set 'Abort after rows' option in the constraint to 1. In the sequential file stage set the 'Cleanup on failure' option to False.
So when you try to insert the same records or records with datatype mismatch the corresponding records will be rejected by the Oracle Enterprise Stage and captured in a sequential file. At the same the time the job will be aborted since you have setup the functionality in the transformer.
HTH
--Rich
Pride comes before a fall