12 Replies Latest reply: Jul 10, 2012 9:48 AM by 882155 RSS

    How to use OracleParameter whith the IN Operator of select statement

    698719
      Hi,

      I am having problems using the OracleParameter when used in a query with a IN operator.

      Following Example:

      oraCommand.Parameters.Add(":List", strMylist ); //or Array???

      The SQL command:
      SELECT * FROM XXX WHERE XXX.YYY IN (:List);

      What datatype must the value of :List be?

      regards
      Stefan
        • 1. Re: How to use OracleParameter whith the IN Operator of select statement
          24208
          Hi Stefan,

          You may find the following technique a possibility:

          http://oradim.blogspot.com/2007/12/dynamically-creating-variable-in-list.html

          Regards,

          Mark
          • 2. Re: How to use OracleParameter whith the IN Operator of select statement
            698791
            Hi,

            keeping your response to Thread "how to pass more than one parameter using common... " in mind, I think the code snippet in that thread addresses this question, too. The datatype of :List is the oracle data-type as it is in the parameter.add statement.

            Regards,
            Dirk
            • 3. Re: How to use OracleParameter whith the IN Operator of select statement
              24208
              Hi Dirk,

              I think one thing that causes problems for people who want to use "in" and pass parameters is that they will often try something like this:

              OracleParameter p = new OracleParameter();
              p.Value = "1,2,3,4";

              and then use p in a SQL statement like:

              select col1 from tab1 where col2 in (:p);

              That will not work. You need to have a separate parameter for each value in the "in" clause. Something like:

              select col1 from tab1 where col2 in (:p1,:p2,:p3,:p4);

              Where each parameter is declared, properties set, etc:

              OracleParameter p1 = new OracleParameter();
              p.Value = 1;

              OracleParameter p2 = new OracleParameter();
              p.Value = 2;

              and so on.

              As some of the people have responded in comments on the blog posting (and they are correct), you will have a separate statement in the cache for each "signature" of the SQL if the number of elements in the list varies:

              select col1 from tab1 where col2 in (:p1);
              select col1 from tab1 where col2 in (:p1,:p2);
              select col1 from tab1 where col2 in (:p1,:p2,:p3);
              select col1 from tab1 where col2 in (:p1,:p2,:p3,:p4);

              But, in my mind, that is better than destroying the cache using a bunch of literals.

              Regards,

              Mark
              • 4. Re: How to use OracleParameter whith the IN Operator of select statement
                339107
                Hi,

                You can hide sql i procedure with one RefCursor parameter and one Varchar2

                CREATE OR REPLACE TYPE NUM_TABLE as table of number
                /
                create or replace function GetNumCollection( iStr varchar2, iSplit char default ',' ) return num_table as
                pStr varchar2(4000) := trim(iStr);
                rpart varchar(255);
                pos pls_integer;
                pColl num_table := num_table();
                begin
                while nvl(length(pStr),0) > 0 loop
                pos := inStr(pStr, iSplit );
                if pos > 0 then
                rpart := substr(pStr,1, pos-1);
                pStr := substr(pStr,pos+1,length(pStr));
                else
                rpart := pStr;
                pStr := null;
                end if;
                if rpart is not null then
                pColl.Extend;
                pColl(pColl.Count) := to_number(rpart);
                end if;
                end loop;
                return pColl;
                end;
                /
                select *
                from ancommon
                where csuniq in (select column_value
                from table(cast(GetNumCollection('1,2,3,4') as num_table)))
                /
                select a.*
                from ancommon a, (select column_value from table(cast(GetNumCollection('1,2,3,4') as num_table))) t
                where a.csuniq = t.column_value
                /
                create or replace procedure testProc(iRefCursor out sys_refcursor, iCollection varchar2) is
                begin
                open iRefCursor for
                select a.*
                from ancommon a, (select column_value from table(cast(GetNumCollection(iCollection) as num_table))) t
                where a.csuniq = t.column_value ;
                end;
                /
                • 6. Re: How to use OracleParameter whith the IN Operator of select statement
                  486393
                  I prefer the binding of a nested table or an associative array of numbers.
                  • 7. Re: How to use OracleParameter whith the IN Operator of select statement
                    Mark Williams-Oracle
                    Like everything each approach should be weighed for whatever works best in a particular environment.

                    Associative Arrays will introduce PL/SQL into the mix (which, of course, is not necessarily bad) and collections will necessitate UDTs.

                    - Mark
                    • 8. Re: How to use OracleParameter whith the IN Operator of select statement
                      Mark Williams-Oracle
                      I'm leaving for holiday later today, but I am curious about the amount of parsing, etc. that will result from the following 3 approaches:

                      - using PL/SQL Associative Arrays for the "in" list and returning a ref cursor
                      - using collections with UDTs and "table"
                      - using bind variables with varying numbers of binds based on number of values

                      If anyone else already has done this, I would find it interesting to see the results... otherwise I will have to wait until after the holiday.

                      - Mark
                      • 9. Re: How to use OracleParameter whith the IN Operator of select statement
                        486393
                        Let's use a collection of User Defined Types (UDT's).


                        First create a table with 1 million rows:
                        create table employees (id number(10) not null primary key, name varchar2(100) );
                        
                        insert into employees 
                        select level l, 'MyName'||to_char(level) 
                        from dual connect by level <= 1e6;
                        
                        1000000 rows created
                        
                        commit;
                        
                        exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);
                        No we turn to the C# code:

                        Let's select employees with id's 3 and 4.

                        Collection type MDSYS.SDO_ELEM_INFO_ARRAY is used because if we use this already predefined Oracle type we don't have to define our own Oracle type. You can fill collection MDSYS.SDO_ELEM_INFO_ARRAY with max 1048576 numbers.
                        using Oracle.DataAccess.Client;
                        using Oracle.DataAccess.Types;
                        
                            [OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
                            public class NumberArrayFactory : IOracleArrayTypeFactory
                            {
                              public Array CreateArray(int numElems)
                              {
                                return new Decimal[numElems];
                              }
                        
                              public Array CreateStatusArray(int numElems)
                              {
                                return null;
                              }
                            }
                        
                        
                            private void Test()
                            {
                              OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
                              b.UserID = "sna";
                              b.Password = "sna";
                              b.DataSource = "ora11";
                              using (OracleConnection conn = new OracleConnection(b.ToString()))
                              {
                                conn.Open();
                                using (OracleCommand comm = conn.CreateCommand())
                                {
                                  comm.CommandText =
                                      @" select  /*+ cardinality(tab 10) */ *  " +
                                      @" from employees, table(:1) tab " +
                                      @" where employees.id = tab.column_value";
                        
                                  OracleParameter p = new OracleParameter();
                                  p.OracleDbType = OracleDbType.Array;
                                  p.Direction = ParameterDirection.Input;
                                  p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
                                  p.Value = new Decimal[] { 3, 4 };
                        
                                  comm.Parameters.Add(p);
                        
                                  int numPersons = 0;
                                  using (OracleDataReader reader = comm.ExecuteReader())
                                  {
                                    while (reader.Read())
                                    {
                                      MessageBox.Show("Name " + reader[1].ToString());
                                      numPersons++;
                                    }
                                  }
                                  conn.Close();
                                }
                              }
                            }
                        The index on employees.id isn't used when one omits hint /*+ cardinality(tab 10) */. This index is created by Oracle because id is the primary key column.

                        Edited by: wateenmooiedag on Dec 30, 2009 2:45 AM
                        • 10. Re: How to use OracleParameter whith the IN Operator of select statement
                          Mark Williams-Oracle
                          Well, I finally got around to testing out a couple of scenarios this evening - primarily the UDT approach as your latest update here shows as well as the original "build a dynamic in clause with bind variables" approach. Long story short, no meaningful differences as far as latching and performance observed. Of course with the original approach one does end up with multiple versions of the statement in the shared pool (based on number of binds). If you've done any measurements as far as latching, etc. I'd be interested if your results showed any significant difference between the measured approaches. I did my tests against an 11.2 database on Solaris (I don't think that will really matter much, but I suppose it could).

                          Regards,

                          Mark
                          • 11. Re: How to use OracleParameter whith the IN Operator of select statement
                            486393
                            I've done a little benchmarking. I measured the execution time of the queries with full retrieval of all the found rows as a function of the number of variables in the in-part. I used the employees table with 1 million rows (see above).

                            My configuration is a two CPU Windows XP 32 bits machine, Oracle 10XE and odp.net 11.1.0.6.20 . Database and client run on the same machine.

                            The maximum number of variables of the "build a dynamic in clause with bind variables" approach is 1000 so that's where my benchmark stopped.
                            Number of variables in the clause     time with UDTs / time with dynamic in clause
                            ---------------------------------     --------------------------------------------
                                 1                                  1.71
                                 2                                  1.55
                                 4                                  1.44
                                 8                                  1.32
                                12                                  1.23
                                20                                  1.09
                                25                                  1.04
                                30                                  1.00
                                35                                  0.97
                                40                                  0.94
                                50                                  0.90
                                75                                  0.83
                               100                                  0.77
                               200                                  0.70
                               500                                  0.68
                              1000                                  0.57
                            So the conclusion is that above 30 variables the UDT approach is faster, below 30 it is better to use the "build a dynamic in clause with bind variables" approach. Didn't take a look at the latches, maybe later.
                            • 12. Re: How to use OracleParameter whith the IN Operator of select statement
                              882155
                              I am a beginner and just wanted to know in the below example,

                              How the program knows that the column name in the table type "tab" is "column_value". Or this is predefined that the only column we can use is column_value

                              *comm.CommandText =
                              @" select /*+ cardinality(tab 10) */ * " +
                              @" from employees, table(:1) tab " +
                              @" where employees.id = tab.column_value";*