Re: Dynamic SQL to disable constraints.

Louis Frolio wrote:

> 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

vString := 'ALTER TABLE job_history DISABLE CONSTRAINT ' || vConName; EXECUTE IMMEDIATE vString;

Your usae of the USING clause serves no purpose as vConName is not a bind variable and can not be a bind variable.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)