Okay, here's a problem I have yet to find an elegant solution for: a variable number of binds with NDS.
NDS allows you to build whatever SELECT you want, then do:
OPEN RefCurVariable FOR SQLVariable USING BindVariable1, BindVariable2, ...
Now, the situation is generally such that the user can provide any combination of values in a bunch of fields on-screen. So, I have a lot of complicated IFs to determine exactly what pieces of SQL to put together. Of course, some of these pieces use BIND variables and some do not. So when I'm done, I have this wonderful SQL with a lot of binds. Now that I'm ready to execute the statement, I need to know exactly what variables to put in the USING clause. The problem? THIS IS NOT DYNAMICALLY-DEFINABLE. Thank you, Oracle!:(
My choices appear to be:
1 - Reproduce all the complicated IFs to end up with exactly the right OPEN...FOR...USING statement for each permutation.
2 - When building the SQL, make sure that every possible bind variable *always* appears in the statement and in the right order...
IF ( p_ORG_PK IS NOT NULL ) THEN
l_SQL := l_SQL || 'AND ORG_PK = :ORG_PK ';
l_SQL := l_SQL || 'AND :ORG_PK IS NULL ';
3 - Create an 'array' of strings to hold the bind variables. Then, each time I add a bind variable to the SQL string, add the accompanying value to the array and increment the count. Then I need a procedure that has all the OPEN...FOR...USINGs with from 0..n variables in the USING clauses and call the right one based on the size of the array.
The third choice is where I'm going now as I've pretty much already exhausted the usefulness of the first 2 choices. In choice 1, the PL/SQL code quickly becomes un-maintainable. In choice 2, the SQL built within the PL/SQL eventually becomes un-maintainable (The current case is up to 9 distinct variables, some of which may or may not be used more than once). Choice 3 is at least somewhat generic and extensible, but I STILL DON'T LIKE IT!!
So I'm wondering if anyone has come up with any other possible solutions for this problem.
Also, I haven't read up enough on 9i yet to know if this crippled implementation was changed or not, so feel free to enlighten me