10 Replies Latest reply: Apr 18, 2013 12:44 AM by Billy~Verreynne RSS

    How to pass a list as bind variable?

    Blais
      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
          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
            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
              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?
                Billy~Verreynne
                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
                  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
                    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?
                      Billy~Verreynne
                      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
                        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
                          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?
                            Billy~Verreynne
                            Sorry, do not use SQL-Developer.