add parameter for in clause in oracledatatable select

add parameter for in clause in oracledatatable select

Postby katsifos » Thu 27 Aug 2015 16:20

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
katsifos
 
Posts: 5
Joined: Fri 19 May 2006 07:13

Re: add parameter for in clause in oracledatatable select

Postby Pinturiccio » Mon 31 Aug 2015 14:54

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();
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: add parameter for in clause in oracledatatable select

Postby katsifos » Thu 03 Sep 2015 11:34

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
katsifos
 
Posts: 5
Joined: Fri 19 May 2006 07:13

Re: add parameter for in clause in oracledatatable select

Postby Pinturiccio » Mon 07 Sep 2015 14:00

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/oracle/docs/?Varray.html
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle