PL/SQL and dynamic queries 1

[hilfy]

I'm developing a package that uses dynamic SQL to set up several queries (select statements) - there are three different entry points to the package and lines may or may not be added to the where clauses depending on which entry point (procedure) is called. I'm using the DBMS_SQL routines to do this. I've done this before with another package and it works beautifully.

However, the problem I'm currently working on requires looping through a list of items to set a parameter on the queries, process the data returned, set the parameter to a different value, process the data, etc. until I've gone through all of the list. In the references I have, I can't find information about how to "close" the query so I can reset the parameter to a new value and execute it again. DBMS_SQL.close_cursor frees the cursor handle, so I know that I don't want to do that until I'm done with all of my processing.

Does anyone have any thoughts or information on this? Is there a different way I should look at doing this? Using static queries is not an option at this point for various reasons (not the least of which is the fact that I just spent 3 days breaking down over 1200 lines of SQL into less that 400 lines in constants used to build the queries.)

Thanks!
-D

 

[carp]

You might want to see if it's possible to use EXECUTE IMMEDIATE instead of dbms_sql for your application. It's a lot simpler and more intuitive to use.

 

[hilfy]

You're right - it does look a lot easier. I'll take some time to play with it this afternoon and let you know if I have any problems with it. Thanks!

-D