Declaring a cursor in a procedure based on a passed in parameter
I am trying to pass in a table name into a procedure and use that table name as part of a cursor.
It seems like I need to declare/initialize the cursor after the BEGIN to be able to use the table name in the cursor. I am not sure if my declaration for the cursor is the problem or if this needs to be done a different way.
create or replace procedure something(table_name_in varchar2)as sTable varchar2; sConstraint varchar2; c1 sys_refcursor;BEGIN OPEN c1 FOR select table_name, constraint_name from user_constraints where table_name = table_name_in; LOOP FETCH c1 INTO sTable, sConstraint; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE;END;
Open in new window