How get more sql errors??

[dbbyleo]

Hi,

I have a procedure where I have put in a simple exception body to capture the sql error (if recieved) during the execution of the procedure. For example:

exception
when others then
v_sqlerrm := sqlerrm;
... (and so on)

This eventually emails the string captured in v_sqlerrm.

However, what I find is this. If I remove the exception body and the procedure errors (on purpose), this is what I would normally get from SQLPLUS Worksheet:

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "my.procedure", line 243
ORA-06512: at line 1

However, when the exception bidy is in place, the string that is ONLY captured is this:

ORA-01422: exact fetch returns more than requested number of rows

How do I do it so that I get all the error messages that normally appears in SQLPLUS Worksheet??

[dbbyleo]

I read in a reference that the sqlerrm function returns the recent sql error. I would guess that those errors I can see in SQLPLUS Worksheet are errors that are raised in succession.

This sqlerrm fact seems to make it not possible to capture the previous errors - errors which are informative in determine why, or better yet, where in the procedure errored in the first place - in this case, it "return too many rows", but it also says which line of code in the procedure it incurred the error....

So, is there any other way to capture all those error messages??

[jmodic]

Use DBMS_UTILITY.FORMAT_ERROR_STACK procedure to display the whole error stack (up to 2000 characters), just as SQL*Plus does.

[dbbyleo]

Are you sure??

I just tried using it and it still only returned the one error. For example, here's all I have in my exception body:

...
exception
when other then
dbms_output.put_line (dbms_utility.format_error_stack);
...

In the execution body of the pl/sql, I entered a bad database link, therefore when the procedure executed, I would get this in sqlplus IF I did not have the the exception body:

ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "my.procedure", line 60
ORA-06512: at line 1

but with the exception I have above, I only get this:

ORA-02019: connection description for remote database not found
PL/SQL procedure successfully completed.

[jmodic]

Are you sure??

I was until now, but now you made me realised I was wrong. :o

You are right, DBMS_UTILITY.FORMAT_ERROR_STACK is pretty useless in this regard, adn SQL*Plus is obviously very far from simply calling this function when displaying the error stack. My appologies for the misleading information....

But if you are on 10g, then obviously there is another function in DBMS_UTILITY that can fulfil your needs: FORMAT_ERROR_BACKTRACE. Check the documentation for how it can be used and what it does. (http://download-west.oracle.com/docs/cd/B12037_01/appdev.101/b10802/d_util.htm#1003800)

[dbbyleo]

Ok, thanks.
Unfortunately, I'm still on 9i. So I'll have to continue looking for solution (any exist) if I want this kind of information to be returned.