Re: Dynamic SQL to disable constraints.

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);