Re: Dynamic SQL Cursors ???
Date: 18 Sep 2002 12:15:37 -0700
Message-ID: <50a5e6b6.0209181115.5b2bf59a_at_posting.google.com>
I have made some changes so that you can see how to use dynamic sql. BTW, I have not tested code at all.
HTH Guang
DECLARE
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);
base_sql varchar2(256); c integer; dum integer; col_name varchar2(30);
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;
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;
base_sql := 'SELECT DISTINCT ' || V_1_COLUMN || ' FROM ' || V_1_TABLE || ' WHERE ' || V_1_COLUMN || ' NOT IN (SELECT ' || V_2_COLUMN || ' from ' || V_2_TABLE || ')';
c := dbms_sql.open_cursor;
dbms_sql.parse(c, base_sql, dbms_sql.native);
dbms_sql.define_column(c, 1, col_name, 30);
dum := dbms_sql.execute(c);
loop
if dbms_sql.fetch_rows(c)>0 then dbms_sql.column_value(c, 1, col_name); DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| V_2_TABLE||' ('||V_2_COLUMN||') VALUES ( '||V_1_COLUMN||');'); else dbms_sql.close_cursor(c); end if;
end loop;
END IF;
END LOOP;
END;
/
"Michael Lackey" <michael.lackey_at_shaw.ca> wrote in message news:<ruSh9.357072$f05.17926758_at_news1.calgary.shaw.ca>...
> 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;
> /
Received on Wed Sep 18 2002 - 21:15:37 CEST