Dapper OracleDynamicParameters, multipleQquery and clause 'in'

[figueiredorj]

Hi,
I am struggling with something not sure why not able to execute.

I have a clause in that I would like to execute in a second query...
for simplicity let's say something like :

`

begin

  open :c_query1 for
    select * from T1;

  open :c_query2 for
  select * from T2 t_t2 where t_t2.name in :pNames;

end;

`

where I would have my parameters as
`

            var dynParams = new OracleDynamicParameters();
            dynParams.Add(":c_query1", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
            dynParams.Add(":c_query2", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
            dynParams.Add(":pNames", new[] {"AA", "BB"});

`

when I execute this my "in clause" comes empty....
any idea?
thanks

[theumairtahir]

Hello!
Can you please show that how are you declaring the "pNames" parameter into the procedure?

[figueiredorj]

Hi @ut786
actually it is a text query....

I am able to query "in clause" with DynamicParameters... however when I change it for OracleDynamicParameters then query is as if no collection passed on....

[theumairtahir]

Actually I couldn't get what you are actually doing. But somehow I get what you want to do. I am showing you the sample on how you can achieve that.
First change pNames type to VARCHAR2 then write the query like this in the procedure:
open :c_query2 for 'select * from T2 t_t2 where t_t2.name in ('|| :pNames';
Then in the C# code:
var values = new string[]{"AA", "BB"};
dynParams.Add(":pNames", direction: ParameterDirection.Input, dbType: OracleMappingType.VARCHAR2, value: string.Join(',', values));

[figueiredorj]

Hi @ut786 ,
that is what I already doing (string concatenation) and was trying to avoid... as for that is always risk of sql injection, I would say....
and actually using "Aggregate" - cleaner....

[theumairtahir]

Hi @ut786 , that is what I already doing (string concatenation) and was trying to avoid... as for that is always risk of sql injection, I would say.... and actually using "Aggregate" - cleaner....

Try to use DataTable in instead of string array.