 | |
#1
 
| |||
| |||
|
Huge Query -
10-02-2008
, 07:53 AM
#2
| |||
| |||
|
|
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. |
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.
#3
| |||
| |||
|
|
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. |
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.
#4
| |||
| |||
|
|
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. |
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.
#5
| |||
| |||
|
|
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. |
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.
#6
| |||
| |||
|
|
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. |
http://download.oracle.com/docs/cd/B...009.htm#i13240
You could perhaps write a shell script to append a 1000 'aa',- lines
to a select, then add from dual...
jg
--
@home.com
Da plane, boss, they found da plane!
#7
| |||
| |||
|
|
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. |
http://download.oracle.com/docs/cd/B...009.htm#i13240
You could perhaps write a shell script to append a 1000 'aa',- lines
to a select, then add from dual...
jg
--
@home.com
Da plane, boss, they found da plane!
#8
| |||
| |||
|
|
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. |
http://download.oracle.com/docs/cd/B...009.htm#i13240
You could perhaps write a shell script to append a 1000 'aa',- lines
to a select, then add from dual...
jg
--
@home.com
Da plane, boss, they found da plane!
#9
| |||
| |||
|
|
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. |
http://download.oracle.com/docs/cd/B...009.htm#i13240
You could perhaps write a shell script to append a 1000 'aa',- lines
to a select, then add from dual...
jg
--
@home.com
Da plane, boss, they found da plane!
#10
| |||
| |||
|
|
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
 |
« 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.