Re: Trick to generate trigger codes for some of the tables
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: Sreejith.Sreekantan@xxxxxxxxxx
- Date: Mon, 6 Dec 2010 12:33:06 +0000
Sreejith
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@xxxxxxxxxx>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) .
>
>
Other related posts:
