This discussion is archived
10 Replies Latest reply: Apr 17, 2013 10:44 PM by BillyVerreynne RSS

How to pass a list as bind variable?

Blais Newbie
Currently Being Moderated
How can I pass a list as bind variable in Oracle?

The following query work well in SQL Developer if I set ":prmRegionID=2".

SELECT COUNTRY_ID,
COUNTRY_NAME
FROM HR.COUNTRIES
WHERE REGION_ID IN (:prmRegionID);


The problem is that I can't find how to set ":prmRegionID=2,3".

I know that I can replace ":prmRegionID" by a substitution variable "&prmRegionID". The above query work well with"&prmRegionID=2" and with "&prmRegionID=2,3".

But with this solution, I lost all advantage of using binds variables (hard parse vs soft parse, SQL injection possibility, etc.).

Can some one tell me what is the approach suggest by Oracle on that subject? My developer have work a long time too find how but didn't found any answer yet.


Thank you in advance,


MB
  • 1. Re: How to pass a list as bind variable?
    dpapde Explorer
    Currently Being Moderated
    One way of doing this is to write it as a stored procedure/function and call it repeatedly by passing different value.

    Another way is to
    1) write all variables in a flat file in a different line and
    2) then write 3/4 lines of small script something along below lines until the file is read.
    for bind_variable_n `cat filename`
    execute "stored_procedure_function(bind_variable_n) ;
  • 2. Re: How to pass a list as bind variable?
    sb92075 Guru
    Currently Being Moderated
    SELECT COUNTRY_ID,
    COUNTRY_NAME
    FROM HR.COUNTRIES
    WHERE REGION_ID IN (SELECT REGION_ID FROM EXTERNAL TABLE);
  • 3. Re: How to pass a list as bind variable?
    sybrand_b Guru
    Currently Being Moderated
    Can you please explain why you keep reposting this and ignore answers in other threads?
    Did you even give it a shot?

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: How to pass a list as bind variable?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Blais wrote:

    The problem is that I can't find how to set ":prmRegionID=2,3".
    Wrong problem. Setting the string bind variable to that means creating a single string that contains the text "+2,3+". THE STRING DOES NOT CONTAIN TWO VALUES.

    So the actual problem is that you are using the WRONG data type - you want a data type that can have more than a single string (or numeric) value. Which means that using the string (varchar2) data type is the wrong type - as this only contains a single value.

    You need to understand the problem first. If you do not understand the problem, you will not realise or understand the solution too.

    What do you want to compare? What does the IN clause do? It deals with, and compares with, a set of values. So it needs a set data type for the bind variable. A set data type enables you to assign multiple values to the bind variable. And use this bind variable for set operations and comparisons in SQL.

    Simple example:
    SQL> --// create a set data type
    SQL> create or replace type TStringSet is table of varchar2(4000);
      2  /
    
    Type created.
    
    SQL> 
    SQL> 
    SQL> var c refcursor
    SQL> 
    SQL> --// use set as bind variable
    SQL> declare
      2          names   TStringSet;
      3  begin
      4          --// assign values to set
      5          names := new TStringSet('BLAKE','SCOTT','SMITH','KING');
      6  
      7          --// use set as a bind variable for creating ref cursor
      8          open :c for
      9                  'select * from emp where ename in (select column_value from TABLE(:bindvar))'
     10          using names;
     11  end;
     12  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print c
    
         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7698 BLAKE      MANAGER         7839 1981/05/01 00:00:00       2850                    30
          7788 SCOTT      ANALYST         7566 1987/04/19 00:00:00       3000                    20
          7369 SMITH      CLERK           7902 1980/12/17 00:00:00        800                    20
          7839 KING       PRESIDENT            1981/11/17 00:00:00       5000                    10
    
    SQL> 
    SQL> --// alternative set comparison
    SQL> declare
      2          names   TStringSet;
      3  begin
      4          --// assign values to set
      5          names := new TStringSet('BLAKE','SCOTT','SMITH','KING');
      6  
      7          --// use set as a bind variable for creating ref cursor
      8          open :c for
      9                  'select * from emp where TStringSet(ename) submultiset of (:bindvar)'
     10          using names;
     11  end;
     12  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print c
    
         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 1980/12/17 00:00:00        800                    20
          7698 BLAKE      MANAGER         7839 1981/05/01 00:00:00       2850                    30
          7788 SCOTT      ANALYST         7566 1987/04/19 00:00:00       3000                    20
          7839 KING       PRESIDENT            1981/11/17 00:00:00       5000                    10
    
    SQL> 
  • 5. Re: How to pass a list as bind variable?
    BluShadow Guru Moderator
    Currently Being Moderated
    Nice clear demonstration Billy... I've added it to the FAQ: {message:id=9360009}
  • 6. Re: How to pass a list as bind variable?
    chris227 Guru
    Currently Being Moderated
    Hi Billy,

    Is there any issue if i use the built-in sys.odcivarchar2list instead of a user defined type?

    Regards
  • 7. Re: How to pass a list as bind variable?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Do not see why it should not be used, Chris.

    But I have my own TYPE schema that is the container for all my (global/interface classes) which I prefer using instead - so I would be using the TStrings type/class in my code (which I called TStringSet in the sample code for better clarity). :-)
  • 8. Re: How to pass a list as bind variable?
    Blais Newbie
    Currently Being Moderated
    Can you point to this question answer. If this question was so obvious, the Oracle documentation had contain a clear example on that subject. It's not the case.
  • 9. Re: How to pass a list as bind variable?
    Blais Newbie
    Currently Being Moderated
    Hi,
    In SQL developer, it's easy to test a query using standard type bind variable. When we start the query, a pop up ask for a value of the bind variable.

    With the UDT, the interface still asks for standard type bind variable. Do you have an idea on how I can cast a string bind variable (like 1, 2, 10) into a set of NUMBER or of VARCHAR2? This way I would be able launch a standard query in SQL developer to test my query.

    Thank you,

    MB
  • 10. Re: How to pass a list as bind variable?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Sorry, do not use SQL-Developer.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points