Re: dynamic sql - output actual string that includes all values from bind variables...

Hi, Jim!

I appreciate the reply, but I think the problem is that you're not seeing the entire procedure. Perhaps I should have been more clear about that - I didn't provide the entire procedure because I didn't want to bog someone down with coding that's out of the scope of the issue. I wrote the original version of this procedure a year ago because I didn't have time to do it in dynamic sql, and it was just a mess and very difficult to maintain. I finally found some time to revisit it recently, and rewrote the entire processing using dynamic sql and bind variables (which is my first attempt at this). Trust me - while the coding is not perfect - the concept of this approach is what is needed for this process, and the performance puts the original version to shame - no repeating of sql statements that are almost indentical (with a few changes based on the loop variables), and a couple of WHERE clause changes as well. You're not seeing the additional layers of loops that this procedure requires, so even without seeing it - I'm sure you can imagine how many sql statements I would have to modify to accommodate all those loops. If you would like to see the entire procedure (including the old one), I would be happy to send them on to you, but that's really out of the scope of my question.

The bottom line is that the procedure works great, but I want to do more with dynamic sql and bind variables (including modifications on this procedure), and I would like to output the actual sql statement that fires with the EXECUTE IMMEDIATE mysql USING bv1,bv2.

Please let me know if you have any suggestions, or know anyone who might.

Thanks,

Rick Received on Fri Mar 03 2006 - 20:11:04 CST