Flat file EXCEPTION handling

[weberm]

I have a stored procedure which loads a table from a flat file. It basically contains a LOOP which gets a line, builds a record, and then inserts the record into a table. However, the way it handles EXCEPTION errors in the build record process leaves a lot to be desired (for example, if a field is offset incorrectly, it may blow up with a standard date exception, which is bad if a record has more than one date field or it happens in the middle of processing, forcing me to count bits in the load file). Before I spend too much time brain-storming, I am wondering if anyone else has come up with something.

I am considering displaying the file line with the exception error underneath, offset to start at the field with the error...

[...]
WHEN OTHERS THEN
     DBMS_OUTOUT.put_line(c_spacer || buffer);
     DBMS_OUTOUT.put_line(c_spacer || v_offset || SQLERRM);

 

[SantaMufasa]

Weberm,

The "WHEN OTHERS..." that you asserted certainly will work as advertised, but my questions are:

1) Would you like your EXECEPTION section to be more specific with your error handling since more than just this error situation could occur?

2) What behaviour would you like your routine to do after it encounters an error? Would you like it to keep processing subsequent records, or would you like the routine to just abnormally terminate with the first error it encounters?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:24 (11Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:24 (11Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.