CODEWITHSUNDEEP
sqloracle-databasecursor
Stene
Stene

Reputation: 315

Saving result set in variable using a cursor

I have a problem using Oracle SQL to loop over a result set twice.

The problem

I have a cursor that gets me all foreign keys to a given table name. Using the result form this cursor, I loop through all the constraints and disable them. Then I perform a data import and then I need to loop over the same result set and enable them.

CURSOR c_fkeys_inn(tabellnavn IN VARCHAR2)
IS
  SELECT table_name,constraint_name, status
  FROM user_constraints
  WHERE (r_constraint_name,r_owner) IN
    (SELECT constraint_name, owner
    FROM user_constraints
    WHERE owner   ='POP'
    AND table_name=upper(tabellnavn)
  )
  AND STATUS = 'ENABLED';

What I would like to do

My brain jumps directly to a variable. I would like to perform the cursor just once, and then save the result from the cursor to a variable.

Is this possible or are there anything I do to save the result from the cursor and loop twice?

Upvotes: 2

Views: 3341

Answers (1)

Vimal Bhaskar
Vimal Bhaskar

Reputation: 778

Please try this code. I have sightly modified your code to just display the constraint's table names. You can modify the end part of the plsql according to your requirement. Please comment if you have come across any mistakes or issues, thank you.

CREATE or replace PROCEDURE a_proc(name_table varchar)
AS
CURSOR c_fkeys_inn(tabellnavn IN VARCHAR2)
IS
SELECT table_name,constraint_name, status
FROM user_constraints
WHERE STATUS = 'ENABLED'
AND TABLE_NAME=tabellnavn;

names_t  c_fkeys_inn%ROWTYPE;
TYPE c_fkeys IS TABLE OF names_t%TYPE; 
fkeys  c_fkeys;
BEGIN
OPEN  c_fkeys_inn(name_table);
FETCH c_fkeys_inn BULK COLLECT INTO fkeys;
CLOSE c_fkeys_inn;

FOR indx IN 1..fkeys.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(fkeys(indx).table_name);
END LOOP;
END a_proc;

To run the code please run a separate plsql block. Please find a simple and a sample plsql block given below.

begin
a_proc('SUPPLIER');
END;

Upvotes: 4

Related Questions