add parameter for in clause in oracledatatable select

[katsifos]

Hello whow is it possible to add paramaeter in in clasie of oracledatable select command ?
with sql block is like
DECLARE
p_list MYINTS;
sString VARCHAR2(50);
BEGIN
p_list := MYINTS(13,1,4,5,10);
SELECT count(*)
INTO sString
FROM THEATRE where id in (select * from TABLE(CAST(p_list AS MYINTS))) ;
dbms_output.put_line(sString);
END;
/
howcan get that result from oracledatatble ?
Regards
Aris

[Pinturiccio]

Please describe where exactly you want to use a parameter in your query, and for which data type.

Since you want to get the query result to OracleDataTable and use a PL/SQL block as a query, this block must use an OUTPUT parameter of a CURSOR type. Here is an example of your query with an OUTPUT CURSOR parameter.

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");

String commandText =
@"DECLARE
p_list MYINTS;
BEGIN
p_list := MYINTS(13,1,4,5,10);
OPEN :cur FOR SELECT count(*)
FROM THEATRE where id in (select * from TABLE(CAST(p_list AS MYINTS))) ;
END;";

OracleCommand cmd = new OracleCommand(commandText, conn);
cmd.Parameters.Add("cur", OracleDbType.Cursor);
cmd.Parameters["cur"].Direction = ParameterDirection.Output;

OracleDataTable dt = new OracleDataTable(cmd);
dt.Fill();

[katsifos]

hi
I want an oracledatatable for a select like
Select * from tablea
where tablea.id in (:p1)
and p1 is a comma seperated ints

Regards

[Pinturiccio]

You can use VARRAY for this. You can use the following example:

Suppose, you have the following type defined:

Code: Select all

CREATE TYPE TArray1 AS VARRAY (5) OF NUMBER;

Then, you can use the following code:

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");

String commandText = "SELECT * FROM THEATRE WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(:par))";

OracleCommand cmd = new OracleCommand(commandText, conn);

conn.Open();
OracleArray array = new OracleArray("TArray1", conn);
// Set the array value in the following way:
array.Add(10);
array.Add(30);
array.Add(31);

conn.Close();

var par = cmd.Parameters.Add("par", OracleDbType.Array);
par.Value = array;

OracleDataTable dt = new OracleDataTable(cmd);
dt.Fill();

For more information, please refer to https://www.devart.com/dotconnect/oracl ... array.html