Re: Drop constraint in PL@SQL

From: Marc Blum <blum_at_marcblum.de>
Date: Wed, 05 Nov 2003 19:58:11 +0100
Message-ID: <jphiqv4afh5r2r3qv0ltm0krcm217aoil5@4ax.com>

On 5 Nov 2003 10:29:11 -0800, maisonborniol_at_nomade.fr (MaisonBorniol) wrote:

>All,
>
>Could someone tell me why the code following doesn't work ?
>
>It is not possible to drop a table in a PL_at_SQL Loop ?
>
>
>Any help would be appreciated,Tx !
>
>DECLARE
> cursor C1 IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS;
> v_table_name USER_CONSTRAINTS.TABLE_NAME%type;
> v_constraint_name USER_CONSTRAINTS.CONSTRAINT_NAME%type;
>
>BEGIN
> OPEN C1;
> LOOP
> FETCH C1 INTO v_table_name, v_constraint_name;
> EXIT WHEN (C1%NOTFOUND);
>
> ALTER TABLE v_table_name DROP CONSTRAINT v_constraint_name;
>
> END LOOP;
> CLOSE C1;
>END;
Embedded DDL is not supported by PL/SQL.

thou gotta use Native Dynamic SQL:

DECLARE
   cursor cur
   IS
   SELECT TABLE_NAME,

                  CONSTRAINT_NAME 

   FROM USER_CONSTRAINTS;    l_sql varchar2(1234);

BEGIN
   FOR rec IN cur
   LOOP

      my_sql :=  ' ALTER TABLE ' ||  v_table_name || 
                      ' DROP CONSTRAINT ' || v_constraint_name ;
   
      execute immediate l_sql;

   END LOOP;
END;
/ Received on Wed Nov 05 2003 - 12:58:11 CST