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;
i think if you use UPPER ( table_name_in ) or if you ensure that table_name_in variable will always have values in CAPITAL LETTERS then your code should work fine.
Shaju Kumbalath
sTable varchar2;
sConstraint varchar2;
try to declare as
sTable user_constraints.table_name%type;
sConstraint user_constraints.constraint_name%type;
try this:
Open in new window