Oracle parameters with an IN statement?

Question

Got a C # .net application that I need to change. At the moment, the request really does this:

select * from contract where contractnum = :ContractNum 

(very simple, just to show that we use the = parameter and one parameter)

This parameter is read from the Settings.Settings file in a C # application and contains one line. I need to change it to include several contracts, so I suppose I can change SQL to:

 select * from contract where contractnum in (:ContractNum) 

but this does not return any results no matter how I format the string in the parameter.

Is there a way I can get oracle for IN with parameter?

any help appreciated, thanks everyone.

Answer 1

There is still no db that supports evaluating a single string variable containing commas to separate as a single IN clause.

Your parameters are a substring of a variable, so the contents of a comma-delimited variable turn into strings, so you can join this. Or use dynamic SQL, which is an SQL statement created as a string in sproc before the statement is executed.

Answer 2

you can use the pipeline function to convert a row to a table, which can be used with the IN operator. For example (tested with 10gR2):

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

with the following package:

 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 

Your request will look like this:

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

Answer 3

You can use the Oracle number collection as a parameter (bind a variable) when you use ODP.NET as a dataprovider. This works with Oracle 9, 10, or 11 and the release of ODP.net> = 11.1.0.6.20.

A similar solution is possible when you use the Devat.NET dataprovider for Oracle.

Let me select contracts with contracts 3 and 4.

We must use the Oracle type to pass an array of contract numbers to our request.

MDSYS.SDO_ELEM_INFO_ARRAY used because if we use this predefined Oracle type, we do not need to define our own Oracle type. You can fill MDSYS.SDO_ELEM_INFO_ARRAY maximum numbers 1048576.

 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(); } } } 

The index on contract.contractnum is not used if you omit the hint / * + power (tab 10) * /. I assumed that contractnum is the primary key, so this column will be indexed.

See also here: http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879

Answer 4

To use a parameter with an IN statement, you can use this construct:

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

where ContractNum is the type of custom array.

Answer 5

I know that this is an old question, but it is one of several in which the selected answer did not solve my problem, and I do not want to start another thread on this topic, so I will simply outline what I found in my travels in the hope that it might help someone.

I do not work very much with Oracle, but, like in SQL Server, it seems that to pass a table parameter, you need the corresponding UDT (user table), for which you have EXECUTE permissions (I may be wrong). This means that other answers involving the use of the built-in SYS UDT come with some load, and I could not figure out if it is really possible to pass the table to something that is not a PL / SQL stored procedure in the current version of ODP.net.

Secondly, the line parsing solution is an obstacle for all obvious reasons (it is impossible to cache the execution plan or, as Oracle calls it, it does not scale well, etc.)

Therefore, I spent quite a lot of time trying to execute the IN clause using a table parameter in datamart, for which I have only READ permission, before I was blinded by an obvious flash ( on the ASP.net forum no less ). It turns out that Oracle supports Xml queries “natively,” so instead of passing an array of values, you can pass an XML list (if that's all you need). Again, I could be wrong, but it is treated as a valid binding parameter, and this is an example of how easy it is to use (vb.net, ADO.net, ODP.net using the NuGet package):

  Dim xe As New XElement("l", New XElement("i", "ITEM-A"), New XElement("i", "ITEM-B")) Using conn As New OracleConnection(myConnectionString) conn.Open() Using cmd As OracleCommand = conn.CreateCommand() cmd.CommandType = CommandType.Text Dim query As String query = " SELECT s.FOO, q.BAR " & vbCrLf query &= " FROM TABLE1 s LEFT OUTER JOIN " & vbCrLf query &= " TABLE2 q ON q.ID = s.ID " & vbCrLf query &= " WHERE (COALESCE(q.ID, 'NULL') NOT LIKE '%OPTIONAL%') AND " query &= " (s.ID IN (" query &= " SELECT stid " query &= " FROM XMLTable('/l/i' PASSING XMLTYPE(:stid) COLUMNS stid VARCHAR(32) PATH '.')" query &= " )" query &= " )" cmd.CommandText = query Dim parameter As OracleParameter = cmd.Parameters.Add("stid", OracleDbType.NVarchar2, 4000) parameter.Value = xe.ToString Using r As OracleDataReader = cmd.ExecuteReader While r.Read() //Do something End While End Using End Using conn.Close() 

This is more of an observation than a carefully studied solution, so please comment if there is anything inappropriate about this.

EDIT. When using this method, there seems to be a limit of 4000 characters (2000 if NVARCHAR), so I had to watch the search. Informative error message that you get if you go, "ORA-01460: conversion failed or unfounded requested"