c# - tools - oracle database odp net

OracleParameter y cláusula IN

Question

¿Hay alguna manera de agregar un parámetro a una cláusula IN utilizando System.Data.OracleClient?

Por ejemplo:

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
OracleCommand command = new OracleCommand(query, conn);
command.Parameters.Add(":Pram", OracleType.VarChar).Value = "''Ben'', ''Sam''";

Answer 1

Es muy simple en ORACLE.

siguientes pasos:

1.crear el tipo predeterminado en el oráculo

CREATE OR REPLACE TYPE t_varchar_tab AS TABLE OF VARCHAR2(4000);

2.crear la función en el oráculo para separar una cadena como "a, b, c" en '''' a '','' b '','' c ''''

CREATE OR REPLACE FUNCTION in_list(p_in_list IN VARCHAR2)ETURNt_varchar_tab
AS
l_tab t_varchar_tab := t_varchar_tab();
l_text VARCHAR2(32767) := p_in_list || '','' ;
l_idx NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, '','');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;

3: luego usa la siguiente consulta para extraer datos de la tabla

SELECT * FROM TABLE_NAME EMP WHERE IN (SELECT * FROM TABLE(in_list(i_input1)));

4. Parámetro de entrada que pasa de c # .net a Oracle SP como

 cmd.Parameters.Add("i_input1", OracleType.VarChar, 50).Value = "S1,S2";

Answer 2

De esa forma su consulta será:

SELECT * FROM TableName WHERE UserName IN (''''''Ben'''', ''''Sam'''''');

Esos dos nombres se ingresarán como un solo valor.

Echa un vistazo a este hilo de asktom.oracle.com para descubrir cómo obtener una lista dinámica.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Answer 3

Puede hacerlo más fácilmente con ODP.NET :

1. Cree un tipo TABLE en su base de datos:

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);

2. Crea un parámetro de colección:

   OracleParameter param = new OracleParameter();
   param.OracleDbType = OracleDbType.Varchar2;
   param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

3. Rellena el parámetro:

   param = new string[2] {"Ben", "Sam" };

4. Enlace el parámetro a la siguiente consulta:

   SELECT * FROM TableName WHERE UserName IN (TABLE(CAST(:param AS t_varchar2)));

Answer 4

En realidad, también probaría este código:

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
param = new string[2] {"Ben", "Sam" };
OracleCommand command = new OracleCommand(query, conn);
command.ArrayBindCount = param.Length;
command.Parameters.Add(":Pram", OracleType.VarChar).Value = param;

Answer 5

Puede envolverlo en el método OracleCommandExtension:

public static class OracleCommandExtension
{
public static OracleCommand AddParameterCollection<TValue>(this OracleCommand command, string name, OracleType type, IEnumerable<TValue> collection)
{
var oraParams = new List<OracleParameter>();
var counter = 0;
var collectionParams = new StringBuilder(":");
foreach (var obj in collection)
{
var param = name + counter;
collectionParams.Append(param);
collectionParams.Append(", :");
oraParams.Add(new OracleParameter(param, type) { Value = obj });
counter++;
}
collectionParams.Remove(collectionParams.Length - 3, 3);
command.CommandText = command.CommandText.Replace(":" + name, collectionParams.ToString());
command.Parameters.AddRange(oraParams.ToArray());
return command;
}
}

Answer 6

Pregunta anterior, pero me gustaría compartir mi código. Simplemente un método simple para crear una cadena que pueda concatenar a un sql generado dinámicamente, sin perder el rendimiento y la seguridad de los parámetros de vinculación:

 /// <summary>
/// 1 - Given an array of int, create one OracleParameter for each one and assigin value, unique named using uniqueParName
/// 2 - Insert the OracleParameter created into the ref list.
/// 3 - Return a string to be used to concatenate to the main SQL
/// </summary>
/// <param name="orclParameters"></param>
/// <param name="lsIds"></param>
/// <param name="uniqueParName"></param>
/// <returns></returns>
private static string InsertParameters(ref List<OracleParameter> orclParameters, int[] lsIds, string uniqueParName)
{
string strParametros = string.Empty;
for (int i = 0; i <= lsIds.Length -1; i++)
{
strParametros += i == 0 ? ":" + uniqueParName + i : ", :" + uniqueParName + i;
OracleParameter param = new OracleParameter(uniqueParName + i.ToString(), OracleType.Number);
param.Value = lsIds[i];
orclParameters.Add(param);
}
return strParametros;
}

Y use esto:

List<OracleParameter> parameterList = new List<OracleParameter>();
int[] idAr = new int[] { 1, 2, 3, 4};
string idStr = InsertParameters(ref parameterList, idAr, "idTest");
string SQL = " SELECT name FROM tblTest WHERE idTest in ( " + idStr + " ) ";

Answer 7

SELECT * FROM Clients
WHERE id IN (
SELECT trim(regexp_substr(str, ''[^,]+'', 1, level)) strRows
FROM (SELECT :Pram as str from dual ) t
CONNECT BY instr(str, '','', 1, level -1) >0);

Answer 8

La solución no debe contener el carácter de coma ni comillas simples, comillas dobles. Sugiero que use una tabla temporal y luego seleccione de allí. Llene la tabla temporal usando parámetros de comando regulares.

Answer 9

Lo encontré al buscar la misma pregunta, así que me gustaría añadir una respuesta que encontré útil ya que no creo que lo anterior realmente lo logre:

http://forums.asp.net/t/1195359.aspx/1?Using%20bind%20variable%20with%20an%20IN%20clause

Agregaré la respuesta aquí también en caso de que el enlace se vuelva inválido:

Re: Utilizando bind variable con una cláusula IN Dec 17, 2007 06:56 PM | LINK

Debe agregar cada valor por separado. Algo como esto (escribir en una Mac, así que no pude probarlo)

string sql = "select id, client_id as ClientID, acct_nbr as AcctNbr from acct where acct_nbr in ( %params% )";
OracleConnection conn = new OracleConnection(DBConnection);
OracleCommand cmd = new OracleCommand();
List<string> params=new List<string>();
foreach(string acctNbr in AcctNbrs.Split('',''))
{
string paramName=":acctNbr" + params.Count.Tostring();
params.Add(paramName)
OracleParameter parms = new OracleParameter(paramName, OracleType.VarChar);
parms.Value = acctNbr;
cmd.Parameters.Add(parms);
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Replace("%params%",params.ToArray().Join(","));
cmd.Connection = conn;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);

Answer 10

Puede usar un tipo de datos personalizado de Oracle similar a aquí:
http://www.c-sharpcorner.com/code/2191/pass-collection-to-oracle-stored-procedure-from-net-layer.aspx

y aquí:
https://.com/a/31466114/1867157

Primero crea un tipo en Oracle y dale permisos:

CREATE TYPE MYSCHEMA.VARCHAR2_TAB_T AS TABLE OF VARCHAR2(4000);
GRANT EXECUTE ON MYSCHEMA.VARCHAR2_TAB_T TO MYROLE

Luego crea 2 clases:

StringListCustomType.cs

public class StringListCustomType : IOracleCustomType, INullable
{
public const string Name = "MYSCHEMA.VARCHAR2_TAB_T";
[OracleArrayMapping()]
public string[] Array;
#region IOracleCustomType
public OracleUdtStatus[] StatusArray { get; set; }
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object objectStatusArray = null;
Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
StatusArray = (OracleUdtStatus[])objectStatusArray;
}
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, Array, StatusArray);
}
#endregion
#region INullable
public bool IsNull { get; set; }
public static StringListCustomType Null
{
get
{
StringListCustomType obj = new StringListCustomType();
obj.IsNull = true;
return obj;
}
}
#endregion
}

StringListCustomTypeFactory.cs

[OracleCustomTypeMapping(StringListCustomType.Name)]
public class StringListCustomTypeFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
#region IOracleCustomTypeFactory
IOracleCustomType IOracleCustomTypeFactory.CreateObject()
{
return new StringListCustomType();
}
#endregion
#region IOracleArrayTypeFactory
Array IOracleArrayTypeFactory.CreateArray(int numElems)
{
return new string[numElems];
}
Array IOracleArrayTypeFactory.CreateStatusArray(int numElems)
{
return new OracleUdtStatus[numElems];
}
#endregion
}

Entonces puedes agregar un parámetro como este:

dbParameter = new OracleParameter();
dbParameter.ParameterName = "myparamname";
dbParameter.UdtTypeName = StringListCustomType.Name;
dbParameter.OracleDbType = OracleDbType.Array;
if (myarray != null)
{
StringListCustomType newArray = new StringListCustomType();
newArray.Array = myarray;
dbParameter.Value
}
else
{
dbParameter.Value = StringListCustomType.Null;
}

Su consulta se vería así:

SELECT *
FROM MYSCHEMA.MYTABLE
WHERE MYVARCHARFIELD IN (SELECT COLUMN_VALUE
FROM TABLE(CAST(:myparamname AS MYSCHEMA.VARCHAR2_TAB_T)))

Answer 11

Sé que esto fue preguntado hace un tiempo, pero no una respuesta brillante.

Haría algo como esto, discúlpeme por el crudo código psudo

string args[] = {''Ben'', ''Sam''};
string bindList = "";
for(int ii=0;ii<args.count;++ii)
{
if(ii == 0)
{
bindList += ":" + ii;
}
else
{
bindList += ",:" + ii;
}
OracleParameter param = new OracleParameter();
param.dbType = types.varchar;
param.value = args[ii];
command.Parameters.Add(param);
}
query = "select * from TableName where username in(" + bindList + ")";

Entonces, la consulta termina teniendo (: 1,: 2) y cada uno de estos se enlaza por separado.

También hay una pregunta similar aquí: Oracle / c #: ¿Cómo uso las variables de vinculación con las sentencias select para devolver múltiples registros?

Answer 12

Tal vez usando un enfoque diferente

SELECT * FROM SCOTT.EMP WHERE EMPNO IN (SELECT TO_NUMBER(X.COLUMN_VALUE) FROM XMLTABLE(''7788,7900'') X);

o

SELECT * FROM SCOTT.EMP WHERE ENAME IN (SELECT X.COLUMN_VALUE.GETSTRINGVAL() FROM XMLTABLE(''"SCOTT", "JAMES"'') X);

Donde el contenido de XMLTABLE podría ser un parámetro único. Por lo tanto, debe ser utilizable desde cualquier idioma.