Re: Dynamic SQL Cursors ???

From: G M <zlmei_at_hotmail.com>
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

Original text of this message