If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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...

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

I used for task like this something like "condition macro language" where each macro statment has 2 parts:
1 - condition (if i got valid text in this part i workd with second part)
2 - macro text, that genereted to part of sql statment, using REPLACE() function (as a rule).

Now i'm thinking about XSLT as a "condition macro language" and standard oracle transformers for generate sql.

Would using DBMS_SQL help any? That way you could parse the SQL once and then iteratively call DBMS_SQL.BIND_VARIABLE or DBMS_SQL.BIND_ARRAY to bind just what you need (and what you've managed in your array). I know it's not as quick as NDS and involves more code for parsing, opening, closing, etc., but you wouldn't have to worry about having 0..N OPEN...FOR...USING statements around. But you would have to be cognizant of data types for the DBMS_SQL bind routines.

Well, there's another solution but I don't think it's very elegant.
It is necessary to use temporary table but the list of parameters in USING clause can be fixed and you can build the WHERE clause of the query dynamically.

The technique is:
1. Instead of OPEN RefCurVariable FOR ... USING use EXECUTE IMMEDIATE for inserting rows into temporary table
2. OPEN RefCurVariable FOR temporary table

The trick is:
In EXECUTE IMMEDIATE we can use PL/SQL block. In the block we must declare a "dummy" variable for each parameter used in USING clause. This avoids 'not all variables bound' error. Then, in the WHERE clause, we can use any bind variables even in any order.