Dynamic SQL / Exception Handling -00942 - Oracle PL/SQL

Question

Initially i was trying to create an exception to catch a table that didnt exist (-00942). Based on the feedback from both Alex and Raj, i amended the code to use EXECUTE IMMEDIATE. This worked in identifying the table however when i corrected the table from 'employe' to 'employees' which is the table that existed in my DB, I would have expected the code to run without error as was the case when i didnt use EXECUTE IMMEDIATE.

Instead of generating the catch all exception, is it possible for the code to run through without exception when using EXECUTE IMMEDIATE?

EXECUTE IMMEDIATE 'SELECT last_name INTO v_name FROM employees WHERE salary = v_sal';

WHEN OTHERS THEN :g_message := 'Some other error occurred.';

I was hoping for the code to run through without exception.

VARIABLE g_message VARCHAR2(250)

DEFINE p_sal = 12000

DECLARE 
   v_name employees.last_name%TYPE;
   v_sal employees.salary%TYPE := &p_sal;

   table_does_not_exist exception;  
   PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);

BEGIN

   EXECUTE IMMEDIATE
   'SELECT last_name INTO v_name FROM employees WHERE salary = v_sal';

   DBMS_OUTPUT.put_line(v_name);


 EXCEPTION 

   WHEN table_does_not_exist then 
   :g_message := 'table dose not exist';  


   WHEN NO_DATA_FOUND THEN 
   :g_message := 'No employee with a salary of '||TO_CHAR(v_sal);  

   WHEN OTHERS THEN
   :g_message := 'Some other error occurred.';
 END;
 /
 PRINT g_message

Hi Alex - i've updated the question which i believe is now unique. Thanks for the reference. It worked but i have a follow on question — SQL_rookie, Jul 27 '16 at 11:23
@SQL_rookie...Your problem is valid coz when you would had tried to execute your statement, it would not have been able to understand "v_sal" and resulted to another exeception. You code will work if you either hardcode the value or remove that clause. — XING, Jul 27 '16 at 11:41
Ok got it - EXECUTE IMMEDIATE 'Select last_name FROM employees WHERE salary = 12000' INTO v_name; thanks guys — SQL_rookie, Jul 27 '16 at 12:30
Thats correct..:-))..If you want to use the v_sal as a parameter , you must use using clause as well. execute immediate 'SELECT emp_name FROM employee WHERE emp_sal = :v_sal' into v_name USING v_sal; — XING, Jul 27 '16 at 12:36

XING · Answer 1 · 2019-02-07T05:40:40.867

try below:

DECLARE 
   v_name employee.emp_name%TYPE;
   v_sal employee.emp_sal%TYPE := 12000;

   table_does_not_exist exception;  
   PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);

BEGIN

execute immediate
   'SELECT emp_name        
   FROM employee_l
   WHERE emp_sal = v_sal' INTO v_name;

   DBMS_OUTPUT.put_line(v_name);


 EXCEPTION 

   WHEN table_does_not_exist then 
   dbms_output.put_line( 'table dose not exist');  


   WHEN NO_DATA_FOUND THEN 
   dbms_output.put_line(  'No employee with a salary of '||TO_CHAR(v_sal));  

   WHEN OTHERS THEN
   dbms_output.put_line( 'Some other error occurred.');
 END;
 /

Dynamic SQL / Exception Handling -00942 - Oracle PL/SQL

1 Answers1