 | |
#11
 
| |||
| |||
|
Re: Huge Query -
10-02-2008
, 05:27 PM
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
I would too but the answer, in Oracle, is that you can build a query
of 8TB size if you wish using DBMS_SQL.
Go to Morgan's Library at www.psoug.org
Look up the DBMS_SQL package
Scroll down to "EXECUTE_PLSQL_BLOCK"
---------------
Daniel Morgan
Oracle Ace Director
University of Washington
#12
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
I would too but the answer, in Oracle, is that you can build a query
of 8TB size if you wish using DBMS_SQL.
Go to Morgan's Library at www.psoug.org
Look up the DBMS_SQL package
Scroll down to "EXECUTE_PLSQL_BLOCK"
---------------
Daniel Morgan
Oracle Ace Director
University of Washington
#13
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
I would too but the answer, in Oracle, is that you can build a query
of 8TB size if you wish using DBMS_SQL.
Go to Morgan's Library at www.psoug.org
Look up the DBMS_SQL package
Scroll down to "EXECUTE_PLSQL_BLOCK"
---------------
Daniel Morgan
Oracle Ace Director
University of Washington
#14
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
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
#15
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
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
#16
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
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
#17
| |||
| |||
|
|
On Oct 2, 7:53 am, CenturionX <darwinbaldr... (AT) gmail (DOT) 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. |
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
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.