I am new at writing a stored procedures in Oracle 8i and I had a bit of confusion on how to get exception information at runtime. I wanted to record the exception type, message returned, and error code at runtime and my questions on irc seemed to leave a few stumped (most likely due to the way the questions were asked). But I finally did get a chunk of useful code (thanks to Dormir on undernet):
WHEN OTHERS THEN
vErrorCode := SQLCODE;
vErrorMsg := SUBSTR(SQLERRM, 1, 4000);
dbms_output.put_line('Error loading first query for '||trunc(sysdate));
dbms_output.put_line(vErrorCode||' - '||vErrorMsg);
Also, I found a good link on handling exceptions in Oracle: http://courses.csusm.edu/cs643yo/slides/exceptions.htm