| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: reference cursors
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
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.
 |
 |