Dynamic SQL to disable constraints.

From: Louis Frolio <froliol_at_yahoo.com>
Date: 17 Sep 2004 05:51:55 -0700
Message-ID: <94c28610.0409170451.5bdec168@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 Received on Fri Sep 17 2004 - 07:51:55 CDT