Sunday, January 3, 2016

How to use execute immediate in PL/SQL Exception block




CREATE OR REPLACE PROCEDURE p1
IS
   l_id   NUMBER;
BEGIN
   SELECT employee_id
   INTO l_id
   FROM employees
   WHERE employee_id IN (100);
 --WHERE employee_id IN (100,200);

   DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  ' || l_id);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DECLARE
         l_id1   NUMBER;
      BEGIN
         EXECUTE IMMEDIATE 'CREATE TABLE T1(ID NUMBER)';
         DBMS_OUTPUT.put_line ('EMPLOYEE ID NO_DATA_FOUND ==>  ');
      END;

      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  NO_DATA_FOUND');
   WHEN TOO_MANY_ROWS
   THEN
      DECLARE
         l_id1   NUMBER;
      BEGIN
         SELECT employee_id
         INTO l_id1
         FROM employees
         WHERE employee_id IN (100);

         DBMS_OUTPUT.put_line ('EMPLOYEE ID TOO MANY ROWS ==>  ' || l_id1);
      END;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  OTHERS');
END;

BEGIN
   p1;
END;





Thanks
Sajal Agarwal

No comments:

Post a Comment