7 Replies Latest reply on Jan 13, 2015 2:47 PM by GregV

    How to fetch oracle error codes in a query

    2834061

      How to fetch error codes of oracle or warning codes? Like when I create a procedure it shows Procedure created with compilation errors.

      For seeing the errors I need to write show errors;

      Is there any way through which I can get these errors or messages through a sql query?

        • 2. Re: How to fetch oracle error codes in a query
          GregV

          Hi,

           

          You can check the USER/ALL/DBA_ERRORS view.

          • 3. Re: How to fetch oracle error codes in a query
            BluShadow

            those are compilation errors.

            What's wrong with using show errors?  Why would you try and write SQL to do it?  (Note: executing an SQL statement would clear down the existing errors so in principle it's not a good idea)

            • 4. Re: Re: How to fetch oracle error codes in a query
              Karthick2003

              BluShadow wrote:

               

              those are compilation errors.

              What's wrong with using show errors?  Why would you try and write SQL to do it?  (Note: executing an SQL statement would clear down the existing errors so in principle it's not a good idea)

               

              I am not sure if i understand this. Can you give an example.

               

              It works for me

               

              SQL> select * from user_errors;
              no rows selected
              SQL> create or replace procedure p
                2  as
                3  begin
                4    open rc for select * from emp;
                5  end;
                6  /
              Warning: Procedure created with compilation errors.
              SQL> select * from user_errors;
              NAME                           TYPE           SEQUENCE       LINE   POSITION
              ------------------------------ ------------ ---------- ---------- ----------
              TEXT
              ----------------------------------------------------------------------------------------------------
              ATTRIBUTE MESSAGE_NUMBER
              --------- --------------
              P                              PROCEDURE             2          4          3
              PL/SQL: SQL Statement ignored
              ERROR                  0
              P                              PROCEDURE             1          4          8
              PLS-00201: identifier 'RC' must be declared
              ERROR                201
              
              SQL> select * from empxx;
              select * from empxx
                            *
              ERROR at line 1:
              ORA-00942: table or view does not exist
              
              SQL> select * from user_errors;
              NAME                           TYPE           SEQUENCE       LINE   POSITION
              ------------------------------ ------------ ---------- ---------- ----------
              TEXT
              ----------------------------------------------------------------------------------------------------
              ATTRIBUTE MESSAGE_NUMBER
              --------- --------------
              P                              PROCEDURE             2          4          3
              PL/SQL: SQL Statement ignored
              ERROR                  0
              P                              PROCEDURE             1          4          8
              PLS-00201: identifier 'RC' must be declared
              ERROR                201
              
              SQL>
              

               

              The user_errors view is not disturbed here.

              • 5. Re: Re: How to fetch oracle error codes in a query
                BluShadow

                My mistake, I could have sworn that if you didn't query the errors immediately then they became lost.  I think I was thinking of the pseudo columns in PL code where you need to check immediately following the SQL statement.  I guess I shouldn't try and assist on the forums when I'm overworked....

                • 7. Re: How to fetch oracle error codes in a query
                  GregV

                  I think the errors disappear only when the related procedure is compiled successfully.