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

Thank you all for the hints.

Is there any way, I can output and see if the syntax is correct ? 

DBMS_OUT doesn't seems to work.

Thank You,

Kind Regards,
Sreejith Nair




From:   Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
To:     Sreejith.Sreekantan@xxxxxxxxxx
Cc:     oracle-l@xxxxxxxxxxxxx
Date:   12/06/2010 06:01 PM
Subject:        Re: Trick to generate trigger codes for some of the tables
Sent by:        nigel.cl.thomas@xxxxxxxxx



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.

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) . 







DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."




Other related posts: