6 Replies Latest reply on Jul 28, 2010 11:41 AM by 737905

    How to catch PL/SQL: ORA-00904 error?

    HARMEETKAUR
      Hi ,
      I am trying to run the following PL/SQL Block and have trapped error code -904( for invalid column name) in the exception section block.

      The column name in the select query does not exist in the emp table and as per my understanding since I have an exceptiion handler for this error it should be handled in Exception Block.

      DECLARE
      invalid_column exception;
      pragma exception_init (invalid_column,-00904);
      no number;
      BEGIN
      select empn into no from emp
      where ename='king';
      EXCEPTION
      when invalid_column then
      dbms_output.put_line(Column ane does not exist)
      );
      end;
      /

      1) Can You please let me know why this is not getting caught in Exception handler
      *2) What changes should I make in Exception handler section so that Invalid column name is handled in the program*
      Thanks and regards
      Harmeet
        • 1. Re: How to catch PL/SQL: ORA-00904 error?
          Saubhik
          SQL> set serverout on
          SQL> DECLARE
            2  invalid_column exception;
            3  pragma exception_init (invalid_column,-00904);
            4  eno number;
            5  BEGIN
            6  EXECUTE IMMEDIATE 'select empn from emp where ename=''king''' INTO eno;
            7  EXCEPTION
            8  when invalid_column then
            9  dbms_output.put_line('Column ane does not exist');
           10  end;
           11  /
          Column ane does not exist
          
          PL/SQL procedure successfully completed.
          Please read about compiler and run time engine in PL/SQL.
          An exception handler processes a raised exception (*run-time* error or warning condition)
          http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/exception_handler.htm#LNPLS01316
          http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/errors.htm#LNPLS00701
          • 2. Re: How to catch PL/SQL: ORA-00904 error?
            Solomon Yakobson
            There are compile time exceptions and run time exceptions. Static PL/SQL allows to catch and handle run time exceptions. It can't handle compile time exceptions. That is why your code does not catch 00904. The only way to catch it is to call:
            DECLARE 
            invalid_column exception;
            pragma exception_init (invalid_column,-00904);
            no number;
            BEGIN
            select empn into no from emp
            where ename='king';
            end;
            /
            dynamically.

            SY.
            1 person found this helpful
            • 3. Re: How to catch PL/SQL: ORA-00904 error?
              781735
              Table names and Column names should be known at the time of writing a code. I wonder why you have to check for invalid column name error. Sounds very strange.

              You can always describe the table and verify that you are using the correct column name in your query.
              1 person found this helpful
              • 4. Re: How to catch PL/SQL: ORA-00904 error?
                Karthick2003
                1) Can You please let me know why this is not getting caught in Exception handler
                Because the Exception is raised during Parsing. And EXCEPTION block is hit only during execution time.
                *2) What changes should I make in Exception handler section so that Invalid column name is handled in the program*
                For that you need to build the query dynimically. But why do you want to do something like that?
                1 person found this helpful
                • 5. Re: How to catch PL/SQL: ORA-00904 error?
                  HARMEETKAUR
                  Thanks a lot for your help.
                  • 6. Re: How to catch PL/SQL: ORA-00904 error?
                    737905
                    Check this:
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  DECLARE
                      2  invalid_column EXCEPTION;
                      3  PRAGMA EXCEPTION_INIT(invalid_column,-00904);
                      4  v_col_check NUMBER := 0;
                      5  v_col_name VARCHAR2(10) := 'OWNE';
                      6  BEGIN
                      7  select COUNT(*) INTO v_col_check
                      8  from all_tab_cols
                      9  where table_name = 'ALL_OBJECTS'
                     10  AND column_name = v_col_name;
                     11  IF (v_col_check = 0) THEN
                     12  RAISE invalid_column;
                     13  END IF;
                     14  EXCEPTION
                     15  WHEN invalid_column THEN
                     16  DBMS_OUTPUT.PUT_LINE('Column '||v_col_name ||' does not exist in table all_objects');
                     17* END;
                    SQL> /
                    Column OWNE does not exist in table all_objects
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> 
                    This way you can check if the column is valid or not in case you are dynamically getting the column names
                    1 person found this helpful