Re: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 589;

On Dec 4, 7:59 pm, KevinS <Sear..._at_googlemail.com> wrote:
> Hi all,
>
> The database this is occuring on is  Release 10.2.0.3.0.
>
> Compiling the procedure below is producing the error
>
>          LINE/COL ERROR
>          --------
> -----------------------------------------------------------------
>          18/42        PLS-00801: internal error [*** ASSERT at file
> pdw4.c, line  589;
>                          Unknown expression Expr = 283.;
>                          DISABLE_CONSTRAINTS_PRC__LCR_DEV2__P__211999
> [18, 4]
>
> As far as I can tell it should be able to alter constraints using
> EXECUTE IMMEDIATE.
>
> Does the use of a table complicate things?
>
> Thanks.
>
> CREATE OR REPLACE PROCEDURE Disable_Constraints_prc
> IS
>   TYPE ddl_tab IS TABLE OF VARCHAR2(2000) INDEX BY pls_integer;
>   t_ddl   ddl_tab;
>
>   CURSOR c_consts_to_dis
>   IS
>   select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE
> CONSTRAINT '||constraint_name||';'
>   from user_constraints c, user_tables u
>   where c.table_name = u.table_name;
>
> BEGIN
>
>   OPEN c_consts_to_dis;
>   FETCH c_consts_to_dis
>   BULK COLLECT INTO t_ddl;
>   CLOSE c_consts_to_dis;
>   FORALL i IN t_ddl.first .. t_ddl.last EXECUTE IMMEDIATE t_ddl(i);
>   t_ddl.delete;
>
> EXCEPTION
> WHEN OTHERS
> THEN
>
>   IF c_consts_to_dis%ISOPEN THEN
>   CLOSE c_consts_to_dis;
>   END IF;
>
> RAISE;
> END;
FORALL is only supposed to execute batch DML, and you're trying to make it execute DDL, so the parser errors out because FORALL can't do that and EXECUTE IMMEDIATE is not legal there. Change it to standard loop:

FOR i in t_ddl.first..t_ddl.last LOOP
  EXECUTE IMMEDIATE t_ddl(i);
END LOOP; and the error should go away.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Dec 05 2008 - 01:23:21 CST