wimbaaijens
asked on
How to return line number on error in plsql
If an error occurs in pl/sql code you can show the error in the exception block using sqlcode and sqlerrm or use dbms_utility.format_error_
ASKER
The call stack only tells me who called my procedure and in which line it was called, it doesn't tell me on which line the error occured.
I guess you are using nested procedures, you need to call the "call stack" procedure where the exception is raised. Also read this :
http://asktom.oracle.com/pls/ask/f?p=4950:8:1027284::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:433029981484,%7Bdbms_utility.format_call_stack%7D
Ali
http://asktom.oracle.com/pls/ask/f?p=4950:8:1027284::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:433029981484,%7Bdbms_utility.format_call_stack%7D
Ali
ASKER
I am indeed using nested procedures and am able to determine in which procedure the error occured using the call stack, but I want to know the line number where the error occured not only the procedure where the error occured.
Hi,
I know what you want and I tell you that the solution we use it's currently the only way to have a good trace for errors.
everywhere in the PLSQL source code we have defined a variable mStep PLS_INTEGER and for every major action (DML statement, conversion assignments and so on we increment the step with 10 (for future changes we increment by 10 because some new inserted changes to have range-values for mStep). At the end of every procedure, function we have a general exception block on which we raise further the error bu including in the message the step value... Something like:
procedure x is
mStep PLS_INTEGER:=0;
begin
mStep := 10;
....
mStep := 50;
....
mStep := 150;
....
exception
when others then
rollback;
raise_application_error(-2
end;
best regards,
Marius Nicoras
I know what you want and I tell you that the solution we use it's currently the only way to have a good trace for errors.
everywhere in the PLSQL source code we have defined a variable mStep PLS_INTEGER and for every major action (DML statement, conversion assignments and so on we increment the step with 10 (for future changes we increment by 10 because some new inserted changes to have range-values for mStep). At the end of every procedure, function we have a general exception block on which we raise further the error bu including in the message the step value... Something like:
procedure x is
mStep PLS_INTEGER:=0;
begin
mStep := 10;
....
mStep := 50;
....
mStep := 150;
....
exception
when others then
rollback;
raise_application_error(-2
end;
best regards,
Marius Nicoras
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is what I've been looking for.
Marius your answer is a good one too (I've been using it myself) but it makes large pieces of code hard to read that is why I've been looking for a solution which doesn't need additional lines of code but direcly reads the error stack.
Marius your answer is a good one too (I've been using it myself) but it makes large pieces of code hard to read that is why I've been looking for a solution which doesn't need additional lines of code but direcly reads the error stack.
This work excellent for returning call stack.
But this still does not return line number for procedures.
Any other ideas.
But this still does not return line number for procedures.
Any other ideas.
This error_stack thing only works, if the error is raised in a trigger, for errors in packages you might as well just use sqlerrm.
Conclusion: dbms_utility.format_error_
Conclusion: dbms_utility.format_error_
In PLSQL use
DBMS_OUTPUT.PUT_LINE(dbms_
HTH
Ali