Re: problem retrieving data from session based temporary table

In article <4b270a4e.0204301700.3fab4504_at_posting.google.com>, novaweb_at_iinet.net.au says...
>
>Thanks for responses. I'm replying to all of you here.
>
>The reason I use dynamic sql (and I agree with you all that it should
>be avoided when possible) is that there is a complex WHERE clause that
>is also built, based on 9 parameters passed to the procedure, two of
>which are comma separated lists for inclusion in the WHERE. Add to
>this a parameter (lets call it the operator parameter) which can be
>either 'AND' or 'OR'. Any of the parameters except the operator
>parameter can be null. Based on this info the WHERE is built
>(including Nested Brackets) into a string. Believe me, I'd like to
>avoid Dynamic SQL, but in this case, it is the more elegant.
>
>I omitted the WHERE from the code that I posted because it wasn't
>necessary in relation to the question I was asking. However, I can see
>that I should have mentioned my reasons for using Dynamic SQL, so
>sorry guys.
>

Make sure to use BIND VARIABLES or set cursor_sharing=force before opening this query (and then back to exact after)

Very very very important!

>The Sorting into a temporary table was not the way I wanted to go, and
>since posting, I've found a way to avoid it. Again, I agree that
>temporary tables should be avoided at all cost.
>
>I don't like to use commit in queries either, but Oracle temporary
>tables require this. But no point on getting bogged down on this, I'm
>not using temporary tables :)
>

they do not!

why do you believe they do??

>Paul

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp