Page 1 of 1
add parameter for in clause in oracledatatable select
Posted: Thu 27 Aug 2015 16:20
by 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
Re: add parameter for in clause in oracledatatable select
Posted: Mon 31 Aug 2015 14:54
by 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();
Re: add parameter for in clause in oracledatatable select
Posted: Thu 03 Sep 2015 11:34
by 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
Re: add parameter for in clause in oracledatatable select
Posted: Mon 07 Sep 2015 14:00
by 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