¿Parámetros de Oracle con declaración IN?

Posteriormente a consultar especialistas en esta materia, programadores de varias ramas y profesores dimos con la respuesta al problema y la compartimos en esta publicación.

Solución:

Puede usar una colección de números de Oracle como parámetro (variable de enlace) cuando usa ODP.NET como proveedor de datos. Esto funciona con el servidor Oracle 9, 10 u 11 y la versión ODP.net >= 11.1.0.6.20.

Una solución similar es posible cuando utiliza el proveedor de datos .NET de Devart para Oracle.

Seleccionemos los contratos con contractnum’s 3 y 4.

Tenemos que usar un tipo de Oracle para transferir un array de números de contrato a nuestra consulta.

MDSYS.SDO_ELEM_INFO_ARRAY se usa porque si usamos este tipo de Oracle ya predefinido, no tenemos que definir nuestro propio tipo de Oracle. puedes llenar MDSYS.SDO_ELEM_INFO_ARRAY con un máximo de 1048576 números.

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) */ c.*  " +
      @" from contract c, table(:1) tab " +
      @" where c.contractnum = tab.column_value";

      OracleParameter p = new OracleParameter();
      p.OracleDbType = OracleDbType.Array;
      p.Direction = ParameterDirection.Input;
      p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
      //select contract 3 and 4
      p.Value = new Decimal[]  3, 4 ;
      comm.Parameters.Add(p);

      int numContracts = 0;
      using (OracleDataReader reader = comm.ExecuteReader())
      
        while (reader.Read())
        
           numContracts++;
        
      
      conn.Close();
    
  

El índice en contract.contractnum no se usa cuando se omite la sugerencia /*+ cardinalidad (pestaña 10) */. Supuse que contractnum es el principal key por lo que esta columna será indexada.

Consulte también aquí: http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879

podría usar una función canalizada para transformar un string en una mesa que podría usarse con el IN operador. Por ejemplo (probado con 10gR2):

SQL> select * from table(demo_pkg.string_to_tab('i,j,k'));

COLUMN_VALUE
-----------------
i
j
k

con el siguiente paquete:

SQL> CREATE OR REPLACE PACKAGE demo_pkg IS
  2     TYPE varchar_tab IS TABLE OF VARCHAR2(4000);
  3     FUNCTION string_to_tab(p_string VARCHAR2,
  4                            p_delimiter VARCHAR2 DEFAULT ',')
  5        RETURN varchar_tab PIPELINED;
  6  END demo_pkg;
  7  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY demo_pkg IS
  2     FUNCTION string_to_tab(p_string VARCHAR2,
  3                            p_delimiter VARCHAR2 DEFAULT ',')
  4        RETURN varchar_tab PIPELINED IS
  5        l_string          VARCHAR2(4000) := p_string;
  6        l_first_delimiter NUMBER := instr(p_string, p_delimiter);
  7     BEGIN
  8        LOOP
  9           IF nvl(l_first_delimiter,0) = 0 THEN
 10              PIPE ROW(l_string);
 11              RETURN;
 12           END IF;
 13           PIPE ROW(substr(l_string, 1, l_first_delimiter - 1));
 14           l_string          := substr(l_string, l_first_delimiter + 1);
 15           l_first_delimiter := instr(l_string, p_delimiter);
 16        END LOOP;
 17     END;
 18  END demo_pkg;
 19  /

Package body created

Su consulta se vería así:

select * 
  from contract 
 where contractnum in (select column_value
                         from table(demo_pkg.string_to_tab(:ContractNum)))

Todavía tengo que encontrar una base de datos que admita la evaluación de un solo string variable que contiene comas para separar como único IN cláusula.

Sus opciones son subcadenar la variable para que el contenido de la variable delimitada por comas se convierta en filas, para que luego pueda unirse a esto. O para usar SQL dinámico, que es una instrucción SQL construida como un string en un sproc antes de que se ejecute la sentencia.

---

---

Utiliza Nuestro Buscador