Re: DBMS_SQL will not work with SQL statement > 32K

Hi.

 There is at least 2 possibilities :

1. Use UTL_FILE to generate your trigger source and exec it as SQL script ( I prefer that one ).
2. Look at spec of DBMS_SQL package: PARSE is overloaded. The second version accepts PL/SQL table that contains SQL statement. Each row of that table may contain up to 256 bytes - use it to overcame 32K limit.

 HTH. Michael.

In article <8297ou$lc2$1_at_nnrp1.deja.com>,   kjhealey_at_my-deja.com wrote:
> Is there any way to execute dynamic DDL from PL/SQL when the DDL
> statement is larger than 32K?
>
> We are writing a PL/SQL function that will create Audit Trail like
> Triggers for all of the tables in our database.
>
> The PL/SQL function creates "CREATE TRIGGER..." statements based on
> information in the system_tables and then executes the statements
using
> the DBMS_SQL package.
>
> The problem is that in some cases, the "CREATE TRIGGER..." statement
> becomes larger than 32K and cannot fit in a VARCHAR2 or be passed to
the
> DBMS_SQL.PARSE function.
>
> Any ideas?
>
> Thanks,
> Kevin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 03 1999 - 14:38:00 CST