Run Stored Procedure With Arrays As Parameters and return RefCursor

[wast]

Can you provide an example to Run Stored Procedure With Array As Parameter and return RefCursor?

Tried by example RunStoredProcedureWithArrayAsParameters but this is what I get: PLS-00306: wrong number or types of arguments in call to procedure

parameters.Add("IN_VAR1", Var1.ToArray(), OracleMappingType.Varchar2, ParameterDirection.Input);
parameters.Add("OUT_REFCURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.ArrayBindCount = Var1.Count();

var r = Connection.QueryAsync<Customer>(qry, parameters, commandType: CommandType.StoredProcedure);

//sql
create TYPE ARRAY_TYPE IS TABLE OF VARCHAR2(1000);

PROCEDURE my_procedure(
    IN_VAR1 IN    ARRAY_TYPE DEFAULT NULL,
   OUT_REFCURSOR  OUT   SYS_REFCURSOR
)
...

[epaulsen]

I'm not at work right now, currently on vacation, so no access to Oracle environment to test this. It should just work the same way it would using a OracleCommand.ExecuteReader.

What data type is Var1 ? It should be something that implements IEnumerable<string> e.g

string[] Var1 = new {"foo","bar" }

[wast]

Var1 is typeof List\<string> and then I do ToArray() as you can see in the 1st post.

[epaulsen]

Then I have no idea at the moment, sorry.
My best suggestion is to attempt to do the query using a normal OracleCommand. Dapper and Dapper.Oracle uses that under the hood. If you can make it work using OracleCommand, it should be possible using Dapper.Oracle.

[wast]

Do you have any example with OracleCommand ?

[epaulsen]

Sorry. Try and see if stackoverflow.com can help you.

[aditya119]

I'm late but maybe this will help someone. To call a procedure with array as input parameter, this is what worked for me:

string[] valueArray = value.ToArray(); // value is IEnumerable<string>
Parameters.Add("pi_param", valueArray, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, collectionType: OracleMappingCollectionType.PLSQLAssociativeArray);

It works when SysRefcursor is added as out parameter, just do this: Parameters.Add("po_cursor", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);

Using ArrayBindCount as mentioned in documentation gave an error PLS-00306: wrong number or types of arguments in call to procedure Not sure, but maybe that works when there is only a single parameter?