Re: reference cursors
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 07:06:29 -0000
As a speculative thought:
If the ref cursor is created through a text string,
with dynamic execution, perhaps the parser is taking
out the hints because it mistakenly recognises them
as comments. (You should be able to see the actual
text that arrives in the 10046 trace). If this is the
case, you could experiment with the alternative format
for hint/commenting to see if that bypasses the problem.
Hints can be wrapped by /*+ */
or preceded (as one liners) by --+
so you may have to play about with building strings
including || chr(10) || and so on, toisolate the hint
bit suitably.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: <Stephen.Lee@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, January 30, 2004 5:04 AM
Subject: RE: reference cursors
After playing around some, it looks like when the select is opened via a
refcursor, the hints don't work. If I create a global temporary table, have
the select table dump its load into the temporary table, then open the
refcursor as select * from the temporary table, everything moves right along
again. In more detail (because I'm playing around with this stuff): I
created a row type, then created a table type, then create a variable of
that table type; then do a bulk select into the table type variable. Now I
wish to hell there was a way to pass a pointer to that table type variable
as the refcursor, but I couldn't figure out a way. So I did a loop to
insert the rows of the table type variable into the temporary table; then do
the refcursor on the temporary table. I don't know if I will actually
suggest this as a "solution". It's kind of Rube Goldberg-y and is bound to
complicate application maintenance .. which is the last thing in the world
this application needs.
Anybody got any better ideas (other than re-write the app)? If there is a
way to pass a pointer to the table type variable without having to use the
temporary table gadget, I'm all ears.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
