This content has been marked as final. Show 6 replies
Please read about compiler and run time engine in PL/SQL.
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.
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
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:1 person found this helpful
DECLARE invalid_column exception; pragma exception_init (invalid_column,-00904); no number; BEGIN select empn into no from emp where ename='king'; end; /
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.1 person found this helpful
You can always describe the table and verify that you are using the correct column name in your query.
1 person found this helpful
1) Can You please let me know why this is not getting caught in Exception handlerBecause 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?
Thanks a lot for your help.
Check this:1 person found this helpful
This way you can check if the column is valid or not in case you are dynamically getting the column names
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>