Dynamic SQL Cursors ???
Date: Wed, 18 Sep 2002 03:33:43 GMT
Message-ID: <ruSh9.357072$f05.17926758_at_news1.calgary.shaw.ca>
I am a junor DBA
[Quoted] [Quoted] I have several databases, and schema where the only constraints that were created were the primary key. Now I have created many foreign keys(150) that fail when I try to enable as a referenced record does not exist. My solution was to identify the failed constraint then identify the reference table and field and insert the required record. As I have about six different environments to make the same change to, I have decided to write a script that creates a script to do all of the required inserts. I have attached part of the script as you will see. I have utilized several cursors to identify then missing records and a way to create the script to create the missing records.
The problem is the TOT_INSERT cursor relies on all unknown values i.e. "from table". I expect that I need to use dynamic SQL but have had no luck in finding an example that matches my situation, or good documentation that works for cursors.
I would appreciate any advise with dynamic cursors and with my problem. Regards
DECLARE
michael.lackey_at_shaw.ca
V_1_TABLE VARCHAR2(100); V_2_TABLE VARCHAR2(100); V_1_COLUMN VARCHAR2(100); V_2_COLUMN VARCHAR2(100);
V_4 VARCHAR2(100);
V_CONSTRAINT VARCHAR2(100);
CURSOR failed_constraint IS
SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME, R_CONSTRAINT_NAME,
CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME not like 'SYS%'
AND STATUS <> 'ENABLED'
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME;
CURSOR TOT_INSERT IS
SELECT DISTINCT V_1_COLUMN
FROM V_1_TABLE
WHERE V_1_COLUMN NOT IN (SELECT V_2_COLUMN from V_2_TABLE);
BEGIN FOR failed_record IN failed_constraint LOOP
V_CONSTRAINT := failed_record.R_CONSTRAINT_NAME; V_1_COLUMN := failed_record.COLUMN_NAME; V_1_TABLE := failed_record.TABLE_NAME;
IF failed_record.CONSTRAINT_TYPE = 'R' THEN
SELECT TABLE_NAME, COLUMN_NAME
INTO V_2_TABLE,V_2_COLUMN
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = failed_record.R_CONSTRAINT_NAME;
FOR NEW_REC IN TOT_INSERT LOOP
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| V_2_TABLE||' ('||V_2_COLUMN||')
VALUES ( '||V_1_COLUMN||');');
END LOOP;
END IF;
END LOOP;
END;
/
Received on Wed Sep 18 2002 - 05:33:43 CEST