asked on

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

MikeOM_DBA

Your cursor is OK, what error are you getting?
try this:

-- Etc --
 
BEGIN
    OPEN c1 FOR select table_name, constraint_name
                  from user_constraints
                 where table_name = UPPER(table_name_in);
    LOOP
-- Etc --

Open in new window

Naveen Kumar

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;

Naveen Kumar

sTable varchar2; -- this declaration is incorrect. It should always have length or as given already use %type

sTable varchar2(30); -- if you want to hard code then it should be something like this. change it for other variable as well

ASKER CERTIFIED SOLUTION

tomerbar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Tiger_77

ASKER

Thanks! Exactly what I was looking for. It really wasn't/isn't obvious that curr_table doesn't need to be declared but that is where my problem was.