1 Reply Latest reply: Apr 19, 2013 2:21 AM by Gary Graham-Oracle RSS

    How to pass a list as bind variable in SQL Developer?

      How can I pass a list as bind variable in SQL Developer?

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

      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.).

      I learn how to do that in SQL plus by using UDT in this thread: How to pass a list as bind variable?

      But with this solution, I lost the SQL Developper nice user interface. 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.

      Can some one tell me what is the best approach on that subject?

      Thank you in advance,

        • 1. Re: How to pass a list as bind variable in SQL Developer?
          Gary Graham-Oracle
          Hi Blais,

          Thanks for first trying the SQL and PL/SQL forum before coming here -- that was definitely the right approach, and you got some very good suggestions there. In terms of your requirement for a single bind prompt for the in-clause value list, I think I have a possible solution. I will present it for the case of a character list, so you might have to tweak it for other data types. First add the following to your schema:
          create or replace 
          TYPE bind_tab_typ AS TABLE OF VARCHAR2(4000);
          create or replace 
          FUNCTION comma_to_table(iv_raw IN VARCHAR2) 
          RETURN bind_tab_typ
             ltab_lname dbms_utility.lname_array;
             ln_len     BINARY_INTEGER;
             dbms_utility.comma_to_table(list   => iv_raw
                                        ,tablen => ln_len
                                        ,tab    => ltab_lname);
             FOR i IN 1 .. ln_len LOOP
                PIPE ROW (ltab_lname(i));
             END LOOP;
          Now you can write a query, say for scott.dept, as follows and have Run Statement prompt for the in-clause value list as a single bind variable:
          select *
          from dept
          where dname in (
            select * from table( comma_to_table( :BNDS ))
          When prompted, provide the list of values separated by only commas without any extra blanks.

          I'm not sure if the Varchar2(4000) really needs to be that large. I use it because that's what dbms_utility.lname_array uses.

          SQL Developer Team