Link to home
Create AccountLog in
Avatar of pendlewe
pendleweFlag for United States of America

asked on

Oracle PL/SQL procedue add multiple exceptons, need block format layout for exception handling

Trying for a simplified example code below. I have a working procedure with the following block layout that works AOK. The real one is more complex, with 7 cursors.
My Objective is to add exceptions for cursors C_2 and C_3. These cursors select multiple rows for each row selected by cursor C_1.
I *know* this is kindergarten level, but I cannot find layout syntax that will work.
This is my first post, please correct me as necessary.
Thanks,
Bill

create or replace procedure BP as
table_name varchar2(10);
sql_delete_text varchar2(2000);
sql_insert_text varchar2(2000);
sql_err varchar2(250);

cursor c_1 is
  select distinct table_nm from mytable1;

cursor c_2(table_nm varchar2) is
  select sql_text from tablex X where x.table_name = table_nm;

cursor c_3(table_nm varchar2 is
  select sql_text from tabley Y where y.table_name = table_nm;

BEGIN
 for c1_data in c_1 loop
    exit when c_1%notfound;  -- selects single row
   TABLE_NM := c_1_data.table_name
  for c2_data in c_2(table_nm) loop
     exit when c_2%notfound;
     SQL_DELETE_TEXT := c2_data.sql_text;  
   execute immediate sql_delete_text; -- deletes multiple records from multiple other tables
   end loop;
    for c3_data in c_3(table_nm) loop
     exit when c_3%notfound;
     SQL_INSERT_TEXT := c3_data.sql_text;
   execute immediate sql_insert_text; -- inserts multiple records into multiple other tables
   end loop;
 END LOOP;
 EXCEPTION WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
          sql_err := substr(SQLERRM,1,250);
         DBMS_OUTPUT.PUT_LINE(sql_err);
END;
 
Avatar of ajexpert
ajexpert
Flag of United States of America image

Please read the following links and you will understand the exception handling in PLSQL

In brief
http://plsql-tutorial.com/plsql-exception-handling.htm

In details
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/07_errs.htm
ASKER CERTIFIED SOLUTION
Avatar of jaiminpsoni
jaiminpsoni
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pendlewe

ASKER

Thank you. I seem to do well on some difficult tasks, and barf at the kindergarten level.