Re: Dynamic SQL to disable constraints.

janik_at_pobox.sk (Jan) wrote in message news:<81511301.0409202308.18dcc0b6_at_posting.google.com>...
> if you don`t like writing an extra code (like me) then use this shorter version:
>
>
> -------------------------
> 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
>

One might consider the old way:

set head off
set termout off
set pages 0
set trimspool on
set linesize 132
set feed off
set verify off
spool xyz.sql
select 'spool table_constraints_removed' from dual; SELECT 'alter table '||table_name||' modify constraint '||constraint_name||';' FROM USER_CONSTRAINTS
WHERE table_name like '%WHATEVER%';
spool off
@xyz

So you have a list of what happened, and a script to undo it isn't much harder. (This is all off the top of my head, so test).

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

jg

--
@home.com is bogus.
http://www.bushyoga.com/