DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Variable number of binds in NDS

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  3. #3
    Join Date
    Apr 2001
    Posts
    118
    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

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    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

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width