Re: How do you run a query through DBMS_SQL that is longer than 2000 chars

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/30
Message-ID: <34d455bf.4102208@192.86.155.100>#1/1

A copy of this was sent to "Matt Larson" <matt_larson_at_jdedwards.com> (if that email address didn't require changing) On Fri, 30 Jan 1998 13:14:23 -0700, you wrote:

>I have a procedure that creates triggers on my tables. The create trigger
>statement is often greater than 2000 characters long. I have been using
>DBMS_SQL to create the trigger automatically, but it limits the character
>string which contains the query to only 2000 characters. Any suggestions or
>ideas?
>
>Please email answers to matt_larson_at_jdedwards.com
>
>Thanks in advance,
>Matt
>

In 7.3 and up, there are 2 dbms_sql.parse calls, the second one you can call is described as:

procedure parse(c in integer, statement in varchar2s,

                  lb in integer, ub in integer,
                  lfflg in boolean, language_flag in integer);

--  Parse the given statement in the given cursor.  The statement is not in
--  one piece but resides in little pieces in the PL/SQL table "statement".
--  Conceptually what happens is that the SQL string is put together as
--  follows:
--  String := statement(lb) || statement(lb + 1) || ... || statement(ub);

-- Then a regular parse follows.
-- If "lfflg" is TRUE then a newline is inserted after each piece.

So, using this version of parse, you can send an array of 255 byte strings that will be glued together to make your statement.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 30 1998 - 00:00:00 CST