We help IT Professionals succeed at work.
Get Started

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

Tiger_77
Tiger_77 asked
on
494 Views
Last Modified: 2013-12-19
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

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE