troubleshooting Question

Declaring a cursor in a procedure based on a passed in parameter

Avatar of Tiger_77
Tiger_77 asked on
Oracle Database
6 Comments1 Solution495 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
tomerbar

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros