Re: Huge Query
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 03 Oct 2008 03:14:38 +0200
Message-ID: <48E571FE.9020100@gmail.com>
>
> I'd seriously question the logic of a query that cannot fit into a
> VARCHAR2.
>
> 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.
Date: Fri, 03 Oct 2008 03:14:38 +0200
Message-ID: <48E571FE.9020100@gmail.com>
Ed Prochak schrieb:
> On Oct 2, 7:53 am, CenturionX <darwinbaldr..._at_gmail.com> 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
> VARCHAR2.
>
> 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