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

    How to log line number alongwith SQLCODE and SQLERRM?

    554899
    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
          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 > 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!
          • 2. Re: How to log line number alongwith SQLCODE and SQLERRM?
            Peter Gjelstrup
            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
            • 3. Re: How to log line number alongwith SQLCODE and SQLERRM?
              Centinul
              Centinul Dec 31, 2008 2:57 PM (in response to Peter Gjelstrup)
              Peter,

              That's the same article I linked to in my response :)
              • 4. Re: How to log line number alongwith SQLCODE and SQLERRM?
                Peter Gjelstrup
                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
                • 5. Re: How to log line number alongwith SQLCODE and SQLERRM?
                  Centinul
                  Centinul Dec 31, 2008 3:27 PM (in response to Peter Gjelstrup)
                  You make a good point! :) Happy Holidays!
                  • 6. Re: How to log line number alongwith SQLCODE and SQLERRM?
                    William Robertson
                    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?
                    • 7. Re: How to log line number alongwith SQLCODE and SQLERRM?
                      554899
                      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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                      • 8. Re: How to log line number alongwith SQLCODE and SQLERRM?
                        554899
                        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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                        • 9. Re: How to log line number alongwith SQLCODE and SQLERRM?
                          William Robertson
                          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.
                          • 10. Re: How to log line number alongwith SQLCODE and SQLERRM?
                            554899
                            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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                            • 11. Re: How to log line number alongwith SQLCODE and SQLERRM?
                              William Robertson
                              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
                              • 12. Re: How to log line number alongwith SQLCODE and SQLERRM?
                                554899
                                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