Re: Trick to generate trigger codes for some of the tables

Rather than using EXECUTE IMMEDIATE, try DBMS_SQL.PARSE.

The PARSE procedure also supports the following syntax for large SQL statements:

DBMS_SQL.PARSE (

   c                  IN   INTEGER,
   statement          IN   VARCHAR2S,
   lb                 IN   INTEGER,
   ub                 IN   INTEGER,
   lfflg              IN   BOOLEAN,
   language_flag      IN   INTEGER);

Note:
The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement.

VARCHAR2S is a table of VARCHAR2

See the PL/SQL Packages and Types
book<http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#i997676> .

Regards Nigel

On 6 December 2010 12:10, Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>wrote:

> Hi List,
>
> We have a requirement in generating triggers for a set of configured tables
> in our database ( Oracle 10g) . I am thinking to write a procedure which
> reads the table names configured and create trigger for each table with a
> defined business ( Its just for audit , so the trigger code is same for
> table A and B but the name and columns might differ , the basic logic is
> same.)
>
> The problem I am facing is that , in my generic procedure I am not able
> to generate the trigger string and execute it using 'EXECUTE IMMEDIATE' as
> for some of our tables when all columns and trigger code is appended the
> string goes beyond 4000( defined as VARCHAR2) .
>
>

--
http://www.freelists.org/webpage/oracle-l