Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Binding variables directly in SQL

    We have a situation where a WHERE clause could have up to 5 constraints in the WHERE clause, or as few as 1. Is there a way to write the SQL so that it uses bind variables inline, so that we can have some dynamic function which generates the WHERE clause as needed? Something like

    Code:
    SELECT *
    FROM table
    WHERE status = :A1 and
              initials > :A2 : using 'A' as :A1 and 'CWF' as :A2;
    We'd be using an external application to generate and submit the SQL (ColdFusion).

    -Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use either the DBMS_SQL package, or Native Dynamic SQL (NDS). NDS is easier but the number of bind variables is fixed, so you have to do something like this:

    Code:
    function myfun (p1 in varchar2, p2 in varchar2, ...) return SYS_REFCURSOR is
      q long;
      c SYS_REFCURSOR;
    begin
      q := 'SELECT * FROM table WHERE 1=1 ';
      if p1 is not null then
        q := q || ' AND status = :a1';
      else
        -- :a1 not needed but must still be included in the SQL to match the USING clause
        q := q || ' AND :a1 is null';
      end if;
      ... -- Ditto for the other 4 params
      open c for q using p1, p2, p3, p4, p5;
      return c;
    end;

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I was looking for a purely SQL solution, before I started using any PL/SQL. Currently, our Cold Fusion programmers are talented in using Cold Fusion, and SQL, but not PL/SQL.

    I didn't know if a purely SQL solution existed.

    -Chuck

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE might help; of course is scales poorly.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know anything about Cold Fusion. If it lets you build a SQL statement in a string and then execute it with bind values then a similar approach to my suggestion above in CF rather than PL/SQL might work. Certainly you can do that kind of thing with ASP/.Net

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    The bind variables have to be declared within the SQL when the query itself is presented to ColdFusion.

    -cf

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Actually, I believe what andrewst suggested can be done in SQL. Of course, you said that at least 1 parameter should be passed.

    For example
    Code:
    SQL>
    SQL> create table t (
      2     a       number,
      3     b       number,
      4     c       number,
      5     d       number,
      6     e       number
      7  )
      8  /
    
    Table created.
    
    SQL>
    SQL> insert into t values ( 1, 2, 3, 4, 5 );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    SQL> var a number
    SQL> var b number
    SQL> var c number
    SQL> var d number
    SQL> var e number
    SQL>
    SQL> exec :a := 1
    
    PL/SQL procedure successfully completed.
    
    SQL> select *
      2    from t
      3   where ( ( a = :a or :a is null )
      4     and ( b = :b or :b is null )
      5     and ( c = :c or :c is null )
      6     and ( d = :d or :d is null )
      7     and ( e = :e or :e is null ) )
      8  /
    
             A          B          C          D          E
    ---------- ---------- ---------- ---------- ----------
             1          2          3          4          5
    
    SQL> exec :a := null;
    
    PL/SQL procedure successfully completed.
    
    SQL> exec :b := 2;
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    
             A          B          C          D          E
    ---------- ---------- ---------- ---------- ----------
             1          2          3          4          5
    
    SQL> exec :a := 1; :b := 2
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    
             A          B          C          D          E
    ---------- ---------- ---------- ---------- ----------
             1          2          3          4          5
    I haven't checked the plan on those when they are indexed. But if it sounds for you, you could check that by yourself.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    I see what you're saying. When I did run a quick test to see what happens in the case of indexed columns, though, it looks like they are not used when the query is constructed in this manner:

    Code:
    SQL> set autotrace traceonly
    SQL> select *
      2  from va_summaries
      3  where ui_number = 19;
    
    41 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=28 Bytes=3976)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'VA_SUMMARIES' (TABLE) (Cost=4 Card=28 Bytes=3976)
       2    1     INDEX (RANGE SCAN) OF 'VA_SUMMARIES_PK' (INDEX (UNIQUE)) (Cost=3 Card=28)
    
    SQL> var a number
    SQL> exec :a := 19;
    
    PL/SQL procedure successfully completed.
    
    SQL> select *
      2  from va_summaries
      3  where ui_number = :a or :a is null;
    
    41 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19792 Card=215955 Bytes=30665610)
       1    0   TABLE ACCESS (FULL) OF 'VA_SUMMARIES' (TABLE) (Cost=19792 Card=215955 Bytes=30665610)
    -Chuck

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't want to construct it in that manner. You want to construct it dynamically so that if :a is not null then you execute:
    Code:
    select *
    from va_summaries
    where ui_number = :a;
    ... but if :a is null you execute:
    Code:
    select *
    from va_summaries;
    or if it must have an :a (like NDS requires):-
    Code:
    select *
    from va_summaries
    where :a is null;
    That way you will use an index on ui_number if it makes sense to, but not if it doesn't.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Thanks for your help. In taking this back to the CF programmer he clarified that the WHERE clause can be built dynamically - with bind variables. He was just trying to keep his code simplified in a way that I don't want to get into, to save your time.

    -Chuck

Posting Permissions

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