7 Replies Latest reply: Sep 28, 2013 2:18 PM by Rajat RSS

    Knowing the exact position of error

    Rajat

      Dear ALL,

       

      I wanted to know is it possible to find the exact position of error where the error has actually occured.

      Let me give an example

       

       

      DECLARE

        v_name VARCHAR2(5);

        v_a  NUMBER;

        v_b NUMBER;

        v_c NUMBER;

      BEGIN

        v_name := 'AAAAAAAAA';

        BEGIN

          V_a := 10;

          v_b := 10;

          v_c := v_a + v_b;

        END;

      EXCEPTION

        WHEN OTHERS THEN

          RAISE_APPLICATION_ERROR(-20001,SQLERRM);

      END;   

         

      And the result is

       

      Error report:

      ORA-20001: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

      ORA-06512: at line 15

       

      What i am asking is , is it possible to track that the error has occurred in line number 1.

       

      Regards

      Rajat

        • 1. Re: Knowing the exact position of error
          odie_63

          Sure, just remove the exception handler.

           

          SQL> DECLARE

            2    v_name VARCHAR2(5);

            3    v_a  NUMBER;

            4    v_b NUMBER;

            5    v_c NUMBER;

            6  BEGIN

            7    v_name := 'AAAAAAAAA';

            8    BEGIN

            9      V_a := 10;

          10      v_b := 10;

          11      v_c := v_a + v_b;

          12    END;

          13  END;

          14  /

          DECLARE

          *

          ERROR at line 1:

          ORA-06502: PL/SQL: numeric or value error: character string buffer too small

          ORA-06512: at line 7

          • 2. Re: Knowing the exact position of error
            Ramin Hashimzadeh

            use dbms_utility.format_error_backtrace to get error line number on exception block.

             

             

             

              exception 

              when .....

                   then    dbms_output.put_line('ERROR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);

                           dbms_output.put_line('line: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

              end;

             

            ---

            Ramin Hashimzade

            • 3. Re: Knowing the exact position of error
              Rajat

              Dear RaminHashimzadeh,


              Thanks for your reply. Could you please show me the use of dbms_utility.format_error_backtrace with the above example.

               

              Regards

              rajat.

               

              declare

                v_name VARCHAR2(5);

                v_a  NUMBER;

                v_b NUMBER;

                v_c NUMBER;

              BEGIN

                v_name := 'AAAAAAAAA';

                BEGIN

                  V_a := 10;

                  v_b := 10;

                  v_c := v_a + v_b;

                END;

              EXCEPTION

                WHEN OTHERS THEN

                  DBMS_UTILITY.format_error_backtrace;http://

                  --RAISE_APPLICATION_ERROR(-20001,SQLERRM);

              END;  

                

              Error report:

              ORA-06550: line 15, column 5:

              PLS-00221: 'FORMAT_ERROR_BACKTRACE' is not a procedure or is undefined

              ORA-06550: line 15, column 5:

              PL/SQL: Statement ignored

              06550. 00000 -  "line %s, column %s:\n%s"

              *Cause:    Usually a PL/SQL compilation error.

              *Action:

              • 4. Re: Knowing the exact position of error
                Rajat

                Dear RaminHashimzadeh,

                 

                set serveroutput on;

                declare

                  v_name VARCHAR2(5);

                  v_a  NUMBER;

                  v_b NUMBER;

                  v_c NUMBER;

                BEGIN

                  v_name := 'AAAAAAAAA';

                  BEGIN

                    V_a := 10;

                    v_b := 10;

                    v_c := v_a + v_b;

                  END;

                EXCEPTION

                  WHEN OTHERS THEN

                    dbms_output.put_line('ERROR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);

                    --RAISE_APPLICATION_ERROR(-20001,SQLERRM);

                END;  

                    ERROR: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                 

                Thanks for answering but still it is not showing me the line number from where the exception is originating..

                 

                Regards

                Rajat

                • 5. Re: Knowing the exact position of error
                  Ramin Hashimzadeh

                  i havent got yet oracle now, please try this:

                   

                  declare

                    v_name VARCHAR2(5);

                    v_a  NUMBER;

                    v_b NUMBER;

                    v_c NUMBER;

                  BEGIN

                    v_name := 'AAAAAAAAA';

                    BEGIN

                      V_a := 10;

                      v_b := 10;

                      v_c := v_a + v_b;

                    END;

                  EXCEPTION

                    WHEN OTHERS THEN

                      dbms_output.put_line('ERROR: ' || DBMS_UTILITY.format_error_backtrace);

                      --RAISE_APPLICATION_ERROR(-20001,SQLERRM);

                  END; 

                   

                   

                  and check documentation

                  DBMS_UTILITY


                  ----

                  Ramin Hashimzade

                  • 6. Re: Knowing the exact position of error
                    dariyoosh

                    Hi,

                    Rajat wrote:

                     

                    Thanks for answering but still it is not showing me the line number from where the exception is originating..

                     

                     

                    You need to use both format_error_stack for the error message and format_error_backtrace for the whole stack trace with positions. In addition, you should not rely on sys.dbms_output, the log should be done in a dedicated log table by an autonomous transaction.

                     

                    Here is an example (it's just an example, obviously a classic log table has more organised structure than the one that I define in this example)

                     

                     

                    1. SET SQLBLANKLINES ON;
                    2. ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
                    3. SET SERVEROUTPUT ON;
                    4. DROP TABLE logtab;
                    5. CREATE TABLE logtab
                    6. (
                    7.   logDateTime TIMESTAMP,
                    8.   logMsg      VARCHAR2(4000),
                    9.   userName    VARCHAR2(30)
                    10. );
                    11. CREATE OR REPLACE PROCEDURE writeLog
                    12. (
                    13.   p_logMsg    IN  VARCHAR2,
                    14.   p_userName  IN  VARCHAR2
                    15. )
                    16. AUTHID DEFINER
                    17. IS
                    18.    PRAGMA AUTONOMOUS_TRANSACTION;
                    19. BEGIN
                    20.    INSERT INTO logtab(logDateTime, logMsg, userName)
                    21.        VALUES(sysdate, p_logMsg, p_userName);
                    22.    COMMIT;
                    23. END writeLog;
                    24. /
                    25. SHOW ERRORS;
                    26. /* ************************************************************************ */
                    27. CREATE OR REPLACE PROCEDURE p1
                    28. AUTHID DEFINER
                    29. IS
                    30. BEGIN
                    31.   --
                    32.   --
                    33.   --
                    34.   -- just to raise an exception
                    35.   RAISE no_data_found;
                    36. END p1;
                    37. /
                    38. SHOW ERRORS;
                    39. CREATE OR REPLACE PROCEDURE p2
                    40. AUTHID DEFINER
                    41. IS
                    42. BEGIN
                    43.   p1();
                    44. END p2;
                    45. /
                    46. SHOW ERRORS;
                    47. CREATE OR REPLACE PROCEDURE p3
                    48. AUTHID DEFINER
                    49. IS
                    50. BEGIN
                    51.   p2();
                    52. END p3;
                    53. /
                    54. SHOW ERRORS;
                    55. CREATE OR REPLACE PROCEDURE p4
                    56. AUTHID DEFINER
                    57. IS
                    58. BEGIN
                    59.   p3();
                    60. END p4;
                    61. /
                    62. SHOW ERRORS;
                    63. CREATE OR REPLACE PROCEDURE p5
                    64. AUTHID DEFINER
                    65. IS
                    66. BEGIN
                    67.   p4();
                    68. END p5;
                    69. /
                    70. SHOW ERRORS;
                    71. BEGIN
                    72.   p5();
                    73. EXCEPTION
                    74.    WHEN OTHERS THEN
                    75.    writeLog
                    76.    (
                    77.       p_logMsg=>
                    78.           '******** Begin of Error Stack ********' || '|' ||
                    79.           sys.dbms_utility.format_error_stack || '|' ||
                    80.           '********* End of Error Stack *********' || '|' ||
                    81.           '|' || '|' ||
                    82.           '+++++++ Begin of Error Backtrace +++++++' || '|' ||
                    83.           sys.dbms_utility.format_error_backtrace || '|' ||
                    84.           '++++++++ End of Error Backtrace ++++++++' || '|' ||
                    85.           '|' || '|',
                    86.           p_userName=>USER()
                    87.    );
                    88.    -- Of course there should always be a RAISE here, I just didn't
                    89.    -- write it here in order to not print two times the same stack
                    90.    -- in this example
                    91. END;
                    92. /
                    93. COLUMN logMsg FORMAT a200;
                    94. COLUMN logDateTime FORMAT a30;
                    95. SELECT  t1.logDateTime,
                    96.    replace(t1.logMsg, '|', CHR(10)) logMsg
                    97. FROM  logtab t1;
                    98. DROP PROCEDURE p5;
                    99. DROP PROCEDURE p4;
                    100. DROP PROCEDURE p3;
                    101. DROP PROCEDURE p2;
                    102. DROP PROCEDURE p1;
                    103. DROP TABLE logtab;

                     

                    And the output gives you both error and the position:

                     

                    LOGDATETIME                            LOGMSG
                    ------------------------------ ----------------------------------------
                    28-SEP-13 08.07.51.000000 PM    ******** Begin of Error Stack ********
                                                    ORA-01403: no data found
                                                    ********* End of Error Stack *********
                                                    +++++++ Begin of Error Backtrace +++++++
                                                    ORA-06512: at "TRAINING.P1", line 9
                                                    ORA-06512: at "TRAINING.P2", line 5
                                                    ORA-06512: at "TRAINING.P3", line 5
                                                    ORA-06512: at "TRAINING.P4", line 5
                                                    ORA-06512: at "TRAINING.P5", line 5
                                                    ORA-06512: at line 2
                                                    ++++++++ End of Error Backtrace ++++++++

                     

                    Regards,

                    Dariyoosh

                    • 7. Re: Knowing the exact position of error
                      Rajat

                      Dear RaminHashimzadeh,


                      Thanks buddy . You just save my job. Simple idea , brilliant idea.

                       

                      Regards

                      Rajat