Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to log line number alongwith SQLCODE and SQLERRM?

554899Dec 31 2008 — edited Jan 5 2009

It is suggested (e.g. Tom Kyte advocates this) that the following is a good way to implement WHEN OTHERS clause...



{color:#800080}when others
then
log_error( ...... ); -- log error is an autonomous transaction
RAISE;
end;{color}




...if I use it this way, actual line number where error occurred is not revealed.

What is the way I can log actual code-line-number along with SQLCODE and SQLERRM in a log table.
Also, I need to throw the same to the client. Using RAISE throws the line number of RAISE.




Thanks,
Sam

This post has been answered by Centinul on Dec 31 2008
Jump to Answer

Comments

Centinul
Answer
Depending on your version of Oracle (this feature was added in 10g), you can use: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.

The following article highlights its abilities: [PL/SQL: Tracing Lines|http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html|PL/SQL: Tracing Lines]

Simple example:
SQL > edit
Wrote file sqlplus_buffer.sql

  1  DECLARE
  2     bad_number      NUMBER;
  3  BEGIN
  4     bad_number := TO_NUMBER('A');
  5  EXCEPTION
  6  WHEN OTHERS THEN
  7     DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  8     RAISE;
  9* END;
SQL > /
ORA-06512: at line 4

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 8
Hope this helps!
Marked as Answer by 554899 · Sep 27 2020
Peter Gjelstrup
Hi Sam,

Check this out, maybe you can use parts of it.

[format_error_backtrace|http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html]

Best Regards
Peter
1 person found this helpful
Centinul
Peter,

That's the same article I linked to in my response :)
Peter Gjelstrup
Hi Centinul,

I know, but your post wasn't there :-)

I guess the timestamp is from when you start your reply, maybe it took 7-8 minutes to write your reply?

- Just guessing

Regards
Peter

edit: maybe it's the other way round, I took 7-8 minutes reading through the article, meanwhile you posted.
Nevertheless, two people referring to the same article means it must be good ;-)

Edited by: Peter Gjelstrup on Dec 31, 2008 7:01 AM
Centinul
You make a good point! :) Happy Holidays!
William Robertson
Just out of interest, what do you use SQLCODE for? I see it logged everywhere but I've never seen the information used for anything. Do you have reports that break down errors by category or something?
554899
Well, I agree SQLERRM includes the Oracle error number and probably Its not required to log it separate.

But, if I have a table/report which says my application database code threw these errors, I or someone else can refer to only error code if he wants to.

So, It's just an extra piece of information (with a redundancy, yeah, you are right!! ).


--Sam
554899
Thanks for reply. Impressive and useful article.

Yet again, I would like to know if I can simply log into an error log table the entries as follows-
SQLCODE | Line_No | SQLERRM
        |         |
        |         |
        |         |
--Sam
William Robertson
I or someone else can refer to only error code if he wants to.
And have you ever referred only to the error code?

Regarding line number, Steven Feuerstein's solution in the article linked above is to parse the text using his package "bt", for which he provides the source code.
554899 
And have you ever referred only to the error code?
...sometimes. It was when a report was required from someone stating what all errors occurred over a period of time. They didn't want the description. I don't know how they consume it but I'm getting your point what's the use of logging error code separate if error code doesn't make sense without its description.
Regarding line number, Steven Feuerstein's solution in the article linked above is to parse the text using his package "bt", for which he provides the source code.
...I meant to ask if there is a simpler way. That thing (bt) works fine.


--Sam
William Robertson
I don't think there is a built-in way to get the line number.

btw you can use a ">" character at the start of a line for quoting, for example

{noformat}>{noformat} a line like this

becomes
a line like this
554899
> btw you can use a ">" character at the start of a line for quoting, for example


:D Thanks William.


--Sam
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 2 2009
Added on Dec 31 2008
12 comments
15,494 views