4 Replies Latest reply on Oct 21, 2009 4:05 PM by Frank Kulash

    SqlPlus error messages are daunting, aren't they?

    728959
      Hello guys,

      I am trying to understand the logic of SQLPLUS error messages. I read the User's Guide and Reference but I still don't understand how these line numbers are calculated!!

      I make an example. I am writing some stored procedures in a file merge_procedures.sql, that I am editing in my favourite text editor (Crimson editor). Beside, I have SQL Plus running in a dos windows in XP.

      The file defines 2 packages and various procedures and functions.

      CREATE OR REPLACE PACKAGE STRING_FNC
      AS
      *....*
      END STRING_FNC;
      */*

      CREATE OR REPLACE PACKAGE BODY STRING_FNC
      AS
      *....*
      END STRING_FNC;
      */*

      CREATE OR REPLACE PACKAGE moveSections
      AS
      *.....*

      end moveSections;
      */*

      When I run one of the procedures using EXEC procName(args) I get an error:

      SQL> EXEC moveSections.mergeUserSections('EMPTY_jbowerstest','A01','XC','G201','
      *01.G1','G101','01.G1.G1')*
      BEGIN moveSections.mergeUserSections('EMPTY_jbowerstest','A01','XC','G201','01.G
      *1','G101','01.G1.G1'); END;*

      ***
      ERROR at line 1:
      ORA-00001: unique constraint (DEV_XCSA.SYS_C008826) violated
      ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 68
      ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 88
      ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 156
      ORA-06512: at line 1

      I don't understand how those line numbers are calculated. I mean, why does it claim there is an error at line 1? And how are the subsequent line numbers calculated? i am getting lost since I cannot find any error at the lines indicated.

      Please help me. Also references to documentation would be appreciated.

      Thanks

      TN
        • 1. Re: SqlPlus error messages are daunting, aren't they?
          21205
          Tremal Naik wrote:
          ***
          ERROR at line 1:
          ORA-00001: unique constraint (DEV_XCSA.SYS_C008826) violated
          ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 68
          ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 88
          ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 156
          ORA-06512: at line 1
          read it from the bottom up.. .The orginal exception occured on line 156 (source) of DEV_XCSA.MOVESECTIONS
          • 2. Re: SqlPlus error messages are daunting, aren't they?
            719440
            Or is it topdown ?:|
            SQL> create table t
              2  ( id number primary key
              3  );
            
            Tabel is aangemaakt.
            
            SQL> create procedure t_ins
              2  is
              3  begin
              4  insert into t values( 1 );
              5  insert into t values( 1 );
              6  end;
              7  /
            
            Procedure is aangemaakt.
            
            SQL> create procedure t_run
              2  is
              3  begin
              4  t_ins;
              5  end;
              6  /
            
            Procedure is aangemaakt.
            
            SQL> exec t_run
            BEGIN t_run; END;
            
            *
            FOUT in regel 1:
            .ORA-00001: Schending van UNIQUE-voorwaarde (constraint NOVIO.SYS_C0011180).
            ORA-06512: in "NOVIO.T_INS", regel 5
            ORA-06512: in "NOVIO.T_RUN", regel 4
            ORA-06512: in regel 1
            The constraint is violated by the statement on line 5 of the procedure t_ins. That's the first place to look for an error.
            If you want to know how t_ins is called, you need the look at line 4 of t_run.

            In Tremal's case: here's a statement on line 68 which causes the unique constraint violation, not on line 156.
            1 person found this helpful
            • 3. Re: SqlPlus error messages are daunting, aren't they?
              728959
              Danny Fasen wrote:
              In Tremal's case: here's a statement on line 68 which causes the unique constraint violation, not on line 156.
              correct :) Thanks

              TN
              • 4. Re: SqlPlus error messages are daunting, aren't they?
                Frank Kulash
                Hi,
                Tremal Naik wrote:
                I am trying to understand the logic of SQLPLUS error messages. I read the User's Guide and Reference but I still don't understand how these line numbers are calculated!!
                ...
                SQL> EXEC moveSections.mergeUserSections('EMPTY_jbowerstest','A01','XC','G201','
                *01.G1','G101','01.G1.G1')*
                BEGIN moveSections.mergeUserSections('EMPTY_jbowerstest','A01','XC','G201','01.G
                *1','G101','01.G1.G1'); END;*

                ***
                ERROR at line 1:
                ORA-00001: unique constraint (DEV_XCSA.SYS_C008826) violated
                ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 68
                ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 88
                ORA-06512: at "DEV_XCSA.MOVESECTIONS", line 156
                ORA-06512: at line 1

                I don't understand how those line numbers are calculated. I mean, why does it claim there is an error at line 1? And how are the subsequent line numbers calculated? i am getting lost since I cannot find any error at the lines indicated.
                ORA-06512 errors are additional information about where the error occurred.
                Like Danny, I find it helpful to read messages like this from the top down.
                The stack above says that the real error, ORA-00001, occurred at line 68 of movesections.
                The procedure at line 68 was called from line 88 of movesections. The real error may have occured around this point; for example, if you passed a bad parameter at line 88, then the code around line 68 may be perfect.
                The procedure at line 88 was called form lline 156 of movesections. Again, the real error may be at line 156, or somewhere leading up to line 156.
                The procedure at line 156 was called from line 1 of some anonymous code, such as
                EXEC moveSections.mergeUserSections('EMPTY_jbowerstest','A01','XC','G201','01.G1','G101','01.G1.G1');