RE: Trick to generate trigger codes for some of the tables
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxx>
- To: "Sreejith.Sreekantan@xxxxxxxxxx" <Sreejith.Sreekantan@xxxxxxxxxx>
- Date: Mon, 6 Dec 2010 09:10:10 -0500
You can create a small procedure where you output multiple 100 char lines.
Just break the string up into 100 char chunks and display each chunk separately.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sreejith S Nair
Sent: Monday, December 06, 2010 7:50 AM
To: Nigel Thomas
Cc: nigel.cl.thomas@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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<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<mailto: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:
