Re: problem retrieving data from session based temporary table

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.

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 :)

Paul Received on Tue Apr 30 2002 - 20:00:48 CDT