Re: Huge Query

From: Maxim Demenko <>
Date: Fri, 03 Oct 2008 03:14:38 +0200
Message-ID: <>

Ed Prochak schrieb:
> On Oct 2, 7:53 am, CenturionX <> wrote:

>> Hello everybody,
>> I created a package to build a query.  This query has a bui dynamic
>> quantity os calculated fields and it overflows a simple VARCHAR2(4000)
>> so i'm using a CLOB.
>> My question is: How can i execute this query referencing the variable
>> Is there something like EXECUTE vQuery inside a procedure or package.
>> Thanks.

> I'd seriously question the logic of a query that cannot fit into a
> You might just have to spool it to a file and run it from a client
> (SQL*Plus) session.
> Even then, will it work? Other than a
> SELECT... WHERE ... somecolumn IN ([4k list of values]) ...
> I cannot think of how to write a query to test the 4K limit.

Since machines write sql, i think your concerns are mostly baseless ;-) I had recently to optimize a query generated by Oracle BI which was (formatted) about 98K - output from dbms_xplan.display about 27000 lines. I ended with rewriting that report from scratch...

To OP: you can parse queries stored in clob with dbms_sql only since 11g, but perhaps you can stick with varchar2 or long - in plsql you can have it up to 32k and maybe it is sufficient for you.

Best regards

Maxim Received on Thu Oct 02 2008 - 20:14:38 CDT

Original text of this message