12 Replies Latest reply on Jan 5, 2009 5:51 AM by 554899

How to log line number alongwith SQLCODE and SQLERRM?

554899 Dec 31, 2008 2:31 PM

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

1. Re: How to log line number alongwith SQLCODE and SQLERRM?

Centinul Dec 31, 2008 2:50 PM (in response to 554899)

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 &gt; 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 &gt; /
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!

2. Re: How to log line number alongwith SQLCODE and SQLERRM?

Peter Gjelstrup Dec 31, 2008 2:54 PM (in response to 554899)

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
Like Show 0 Likes(0)

Actions
3. Re: How to log line number alongwith SQLCODE and SQLERRM?

Centinul Dec 31, 2008 2:57 PM (in response to Peter Gjelstrup)

Peter,

That's the same article I linked to in my response :)
Like Show 0 Likes(0)

Actions
4. Re: How to log line number alongwith SQLCODE and SQLERRM?

Peter Gjelstrup Jan 5, 2009 7:20 AM (in response to Centinul)

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
Like Show 0 Likes(0)

Actions
5. Re: How to log line number alongwith SQLCODE and SQLERRM?

Centinul Dec 31, 2008 3:27 PM (in response to Peter Gjelstrup)

You make a good point! :) Happy Holidays!
Like Show 0 Likes(0)

Actions
6. Re: How to log line number alongwith SQLCODE and SQLERRM?

William Robertson Dec 31, 2008 6:07 PM (in response to 554899)

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?
Like Show 0 Likes(0)

Actions
7. Re: How to log line number alongwith SQLCODE and SQLERRM?

554899 Jan 2, 2009 5:25 AM (in response to William Robertson)

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
Like Show 0 Likes(0)

Actions
8. Re: How to log line number alongwith SQLCODE and SQLERRM?

554899 Jan 2, 2009 6:54 AM (in response to Centinul)
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
Like Show 0 Likes(0)

Actions
9. Re: How to log line number alongwith SQLCODE and SQLERRM?

William Robertson Jan 2, 2009 7:28 AM (in response to 554899)

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.
Like Show 0 Likes(0)

Actions
10. Re: How to log line number alongwith SQLCODE and SQLERRM?

554899 Jan 2, 2009 8:13 AM (in response to William Robertson)
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
Like Show 0 Likes(0)

Actions
11. Re: How to log line number alongwith SQLCODE and SQLERRM?

William Robertson Jan 3, 2009 9:39 AM (in response to 554899)

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
Like Show 0 Likes(0)

Actions
12. Re: How to log line number alongwith SQLCODE and SQLERRM?

554899 Jan 5, 2009 5:51 AM (in response to William Robertson)

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

:D Thanks William.

--Sam
Like Show 0 Likes(0)

Actions

Go to original post