Re: Dynamic SQL Cursors ???

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Wed, 18 Sep 2002 15:20:54 GMT
Message-ID: <3D8899D4.708EAF93_at_exesolutions.com>


Michael Lackey wrote:

> I am a junor DBA
> 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;
> /

Take a look at all_cons_columns.

Daniel Morgan Received on Wed Sep 18 2002 - 17:20:54 CEST

Original text of this message