-
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...
Code:
IF ( p_ORG_PK IS NOT NULL ) THEN
l_SQL := l_SQL || 'AND ORG_PK = :ORG_PK ';
ELSE
l_SQL := l_SQL || 'AND :ORG_PK IS NULL ';
END IF;
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 ;)
Any and all thoughts on the matter are welcome.
Thanks,
- Chris
-
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.
May be this information will help to you.
-
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.
Just a thought and I agree that NDS can be annoying because of this. :)
HTH,
Heath
-
Just read an AskTom question that discusses how to use bind variables in dynamic SQL. Just thought I'd add a link to this thread.
http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:2531562164953,
Heath
-
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.
Code:
--the price for this solution: GLOBAL TEMPORARY TABLE
drop table t_emp
/
create global temporary table t_emp
on commit delete rows
as select * from emp where 1=0
/
--
create or replace package dyn_test as
type t_ref_cursor is ref cursor;
procedure dyn_sql(cur in out t_ref_cursor,
sqltext varchar2,
p_name char,
p_sal number);
end dyn_test;
/
--
create or replace package body dyn_test as
procedure dyn_sql(cur in out t_ref_cursor,
sqltext varchar2,
p_name char,
p_sal number) is
block varchar2(200);
begin
--create PL/SQL block
block :=
'declare '||
'dummy_name varchar2(10) := :NAME ; '||
'dummy_sal number := :SALARY ; '||
'begin '||
'insert into t_emp ' ||
sqltext || ';' ||
'end;';
execute immediate block using p_name, p_sal;
open cur for select * from t_emp;
end;
end dyn_test;
/
A. user has defined conditions for ename and sal:
Code:
scott@oracle> var c refcursor
scott@oracle> begin
2 dyn_test.dyn_sql(
3 cur => :c,
4 sqltext => 'select * from emp where ename like :NAME and sal>:SALARY',
5 p_name => 'S%',
6 p_sal => 1000);
7 end;
8 /
PL/SQL procedure successfully completed.
scott@oracle> print c
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000
scott@oracle> commit; -- TO DELETE ROWS FROM t_emp
Commit complete.
B. The same, reverse order of predicates
Code:
scott@oracle> begin
2 dyn_test.dyn_sql(
3 cur => :c,
4 sqltext => 'select * from emp where sal>:SALARY and ename like :NAME',
5 p_name => 'S%',
6 p_sal => 1000);
7 end;
8 /
PL/SQL procedure successfully completed.
scott@oracle> print c
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000
scott@oracle> commit;
Commit complete.
C. User has defined a condition for ename only:
Code:
scott@oracle> begin
2 dyn_test.dyn_sql(
3 cur => :c,
4 sqltext => 'select * from emp where ename like :NAME',
5 p_name => 'S%',
6 p_sal => null);
7 end;
8 /
PL/SQL procedure successfully completed.
scott@oracle> print c
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
7369 SMITH CLERK 7902 17.12.1980 00:00:00 800
7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000
scott@oracle> commit;
Commit complete.
D. User has defined a condition for sal only:
Code:
scott@oracle> begin
2 dyn_test.dyn_sql(
3 cur => :c,
4 sqltext => 'select * from emp where sal>:SALARY',
5 p_name => null,
6 p_sal => 1000);
7 end;
8 /
PL/SQL procedure successfully completed.
scott@oracle> print c
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------- ----------
7499 ALLEN SALESMAN 7698 20.02.1981 00:00:00 1600
7521 WARD SALESMAN 7698 22.02.1981 00:00:00 1250
7566 JONES MANAGER 7839 02.04.1981 00:00:00 2975
7654 MARTIN SALESMAN 7698 28.09.1981 00:00:00 1250
7698 BLAKE MANAGER 7839 01.05.1981 00:00:00 2850
7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450
7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000
7839 KING PRESIDENT 17.11.1981 00:00:00 5000
7844 TURNER SALESMAN 7698 08.09.1981 00:00:00 1500
7876 ADAMS CLERK 7788 12.01.1983 00:00:00 1100
7902 FORD ANALYST 7566 03.12.1981 00:00:00 3000
7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300
12 rows selected.
Just a note to dynamic building of queries:
My approved method is writing queries like
Code:
select * from emp
where 1=1
[ and ename like :ENAME ]
[ and sal > :SALARY ]
If a parameter is undefined I delete the predicate for it and as a last step I replace all [] with a space.
The query is better maintainable than in case of dynamic building from strings;
I hope that's not too confusing, I really tried ...
Ales
