Parsing the exception returned
A reader, August 05, 2005 - 4:30 am UTC
You can also parse the error message in your Java code.
I have already seen such a code sample.
It can show constraint violations.
I already experimented to create an error stack using Oracle objects (type) and pass it back to Java.
Methodology for error trapping is not clear
Aditya Saraogi, August 05, 2005 - 7:44 am UTC
Hi Tom,
You suggest that the error should be allowed to escalate to the highest calling point where it can be handled. My question is even if the error propagates to that calling point, what happens in that program. I mean, I have an Oracle Error Number with me,what am I supposed to do with it? I will have to write nested/multiple catch statements that can trap every possible type of error and deal with every possible failure scenario for every single procedure.
All this is necessary if I dont wish to use the notorious WHEN OTHERS statement. What is the sane limit for checking errors?
What could possibly be the work around for such a scenario?
Thanks
Aditya Saraogi
August 05, 2005 - 11:26 am UTC
you could in turn call that routine that converts the error into something "meaningful to YOUR program". But let the error propagate AS AN ERROR to the client, something "un-ignoreable"
You CANNOT write mutliple catch statements -- for every procedure could return EVERY error (and that is the point here - they are *errors*, unexpected exceptions that NO ONE ANTICIPATED really)
Error Propogation All the Way Up
Kevin Shidler, August 11, 2005 - 3:14 pm UTC
I am a fan of error propogation all-the-way up to the client tier, especially for "unknown errors". They are unknown (similar to ORA-00600 errors). Someone needs to know about them. Certain errors are not considered during application design, but by propogating the error up to the client, during the testing phase of application design, the tester will notify the necessary personnel, and the change will be made. It would be impossible to handle every single error that can occur while creating any application. So why hide it in a clause similar to:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AN ERROR OCCURRED: '||
SUBSTR(SQLERRM,1,200);
END;
Notice the absence of "RAISE". We have effectively handled the error.
I feel like it is re-inventing the ERROR object by returning codes that represent errors, unless those errors are business-rule defined errors for the application. Just propogate the error object back up to whomever the caller is. If you can find a graceful way of handling the error (defined by business rules), then in that situation, you probably don't need to propogate the error once it has been handled. It is all application-dependent based upon the business rules/requirements.
August 12, 2005 - 8:13 am UTC
you have my vote.
if the client doesn't understand the error in production, it should say something like:
"I am sorry, we hit something unexpected, I have invoked the generic log a message and send an email to the support team with all of the information about this error. You may contact the help desk with id = 42525325 to gather more information about the solution for this problem"
Exceptions stored in data dictionary
Vijay, May 02, 2007 - 5:30 am UTC
Dear Tom,
good day to you as always, just out of curiosity wanted to know are the Oracle exception stored in any table I mean any dictionary object of Oracle.
Thanks in advance for your time on this query.
Regards,
Vijay'S
May 02, 2007 - 9:02 am UTC
if you mean error messages (exceptions being a PLSQL programming construct), we can get access to them:
ps$tkyte%ORA10GR2> exec dbms_output.put_line( sqlerrm( -1 ) );
ORA-00001: unique constraint (.) violated
PL/SQL procedure successfully completed.
some more details on this
VijayS, May 02, 2007 - 9:28 am UTC
Dear Tom,
thanks for taking out your time on this, what I intended to know is, from where is the message and error number extracted, is there any table which holds all the error number and messages, if yes which one.
Thanks again for your time on this.
Regards,
Vijay'S
May 02, 2007 - 5:04 pm UTC
I showed you how to do this, there is no table, they are stored in the file system, this function provides access to it.
table
A reader, May 02, 2007 - 10:26 am UTC
create table temp(i integer, errm varchar2(4000));
declare x varchar2(4000);
begin
for i in -99999..0 loop
x:=sqlerrm(i);
insert into temp values(i, x);
end loop;
commit;
end;
/
select * from temp
where errm not like '%non-ORACLE exception%'
and errm not like 'ORA-%Message % not found; product=RDBMS; facility=ORA'
BUT user defined exceptions
A reader, May 02, 2007 - 11:11 am UTC
thanks for the time on this
VijayS, May 06, 2007 - 1:02 pm UTC
Dear Tom,
good day to you as always, thanks for your reply on this, I just wanted to confirm that the exceptions are not stored as such in any tables of the database.
Regards,
VijayS
May 08, 2007 - 10:29 am UTC
no, the error messages are in a file on the server.
pl/sql error propagation to java
Lawrence Raymond, August 11, 2014 - 7:22 pm UTC
Propagating pl/sql excepytions back to the [java] caller has to be the way to go.
As DBA, I rec. using an EXCEPTION WHEN OTHERS section: call an 'async' procedure to write the error message, with identifying info., to an oracle table. Then, do: RAISE.