Re: Dynamic SQL to disable constraints.

BEGIN
  FOR c_const IN (
   SELECT constraint_name

      FROM USER_CONSTRAINTS
    WHERE table_name='T') LOOP
   EXECUTE IMMEDIATE
   'ALTER TABLE T MODIFY CONSTRAINT ' ||c_const.constraint_name||' DISABLE';   END LOOP;
END; jan

froliol_at_yahoo.com (Louis Frolio) wrote in message news:<94c28610.0409201456.3c6c8655_at_posting.google.com>...
> froliol_at_yahoo.com (Louis Frolio) wrote in message news:<94c28610.0409170451.5bdec168_at_posting.google.com>...
> > All, am trying to write dynamic sql to disable constraints on a table.
> > However, it seems that the method I employed is not doing the job:
> >
> >
> > set serveroutput on
> >
> > declare
> > vConName dba_constraints.table_name%TYPE;
> > vString varchar2(1000);
> >
> > cursor cConName is
> > select constraint_name
> > from dba_constraints
> > where table_name = 'JOB_HISTORY' and
> > constraint_type in ('C','PK','R');
> > begin
> > open cConName;
> > loop
> > fetch cConName into vConName;
> > exit when cConName%NOTFOUND;
> > vString := 'alter table ' || 'JOB_HISTORY' || ' disable
> > constraint ' || vConName || ';';
> > --dbms_output.put_line(vString);
> > execute immediate vString using vConName;
> > end loop;
> > close cConName;
> > end;
> >
> > /
> >
> >
> > it is failing on the execute piece. The output properly displays the
> > "alter datbase ..." command though? I think that I may need to employ
> > dbms_sql but I am not sure. Any help would be greatly appreciated.
> >
> > Louis
>
>
> Here is the final solution I deployed:
>
> DECLARE
>
> CURSOR crConstraints IS
> SELECT owner, constraint_name
> FROM dba_constraints
> WHERE constraint_type = 'R'
> AND table_name = 'ORDDET';
>
> v_ConstraintOwner dba_constraints.owner%TYPE;
> v_ConstraintName dba_constraints.constraint_name%TYPE;
>
> inIgnore INTEGER;
> inCSQL INTEGER;
>
> BEGIN
>
> inCSQL:= DBMS_SQL.OPEN_CURSOR;
> OPEN crConstraints;
> LOOP
> dbms_output.put_line(v_ConstraintOwner || '.' ||
> v_ConstraintName);
> FETCH crConstraints INTO v_ConstraintOwner, v_ConstraintName;
> EXIT WHEN crConstraints%NOTFOUND;
> DBMS_SQL.PARSE(inCSQL, 'ALTER TABLE ' || v_ConstraintOwner ||
> '.' || 'ORDDET DISABLE CONSTRAINT '|| v_ConstraintName,DBMS_SQL.V7);
> inIgnore := DBMS_SQL.EXECUTE(inCSQL);
> END LOOP;
> CLOSE crConstraints;
> DBMS_SQL.CLOSE_CURSOR(inCSQL);
> END;
> /
Received on Tue Sep 21 2004 - 06:26:43 CDT