| Very long query with ref cursor - Buffer too small error(2 Merged) [message #512709] |
Tue, 21 June 2011 14:10  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I want to pass a damn long query(which includes a lot of column names, tables, joins, union, where clauses etc. and whose length is more than 120000) in a Ref cursor (that's length is more than 32767). Query is stored in a LONG type variable V_QRY in stored procedure, and I am opening that ref cursor like below-
OPEN P_RPT_TEST FOR V_QRY;
at run time its giveing string buffer too small error.
Please help.
Regards,
Manu
|
|
|
|
|
|
| Re: Very long query with ref cursor - Buffer too small error(2 Merged) [message #512716 is a reply to message #512709] |
Tue, 21 June 2011 15:39  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
This post reminded me this thread: http://www.orafaq.com/forum/m/506017/96705/#msg_506017 Although it deals with different dynamic method (EXECUTE IMMEDIATE vs. OPEN FOR), the dynamic variable limit is the same: from 11g (it would be nice if you posted which version you are using), you may use variable with CLOB data type for storing it.
Otherwise, there is no way for dynamically executing the statement longer than 32767 characters. You have only a few possibilities of shortening it: binding long literal values (if there are any), creating static views from sub selects (if they are static; not a good idea doing it dynamically in PL/SQL). Maybe there are other possible methods, but I am not aware of any.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Very long query with ref cursor - Buffer too small error(2 Merged) [message #512793 is a reply to message #512738] |
Wed, 22 June 2011 05:17 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
manubatham20 wrote on Tue, 21 June 2011 23:23@Syakobson: Can I use DBMS_SQL while referring to a ref cursor and clob variable?
If you are referring to 11g - there is no need to since in 11g OPEN FOR cursor takes CLOB as dynamic statement. But in general you could use DBMS_SQL which in 11g allows to convert DBMS_SQL cursor to ref cursor.
If you are referring to 10g, DBMS_SQL, afaik, is the only way to deal with dynamic SQL longer than 32K. 10g DBMS_SQL does not allow to convert DBMS_SQL cursor to ref cursor. You would have to create [pipelined] table function first and then use:
> OPEN ref_cursor FOR SELECT * FROM TABLE([pipelined]_table_function)
And in 10g DBMS_SQL does not take CLOB. You pass dynamic SQL longer than 32K to DBMS_SQL as a VARCHAR2S associative array (which implies splitting SQL into up to 256 byte chunks) or VARCHAR2A associative array (which implies splitting SQL into up to 32767 byte chunks).
SY.
[Updated on: Wed, 22 June 2011 05:27] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
