ODP.NET

Hello !

New to the forum and I already there with questions ;-) .

I have a problem calling a stored procedure and passing some parameters to the stored procedure call.
I am working unter C#, development runs under VS2008.
Oracle server version is : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
The Oracle Client version for data access is 11.1.0.7.20.

I have a Stored Procedure, called : POLL_DISPATCHER_TASK2, thats how the procedure is defined :

PROCEDURE POLL_DISPATCHER_TASK2(p_dispatcher_name IN VARCHAR2, p_task_types IN T_TASK_TYPES, p_task_status IN VARCHAR2, p_task_async IN VARCHAR2, p_caller_id IN VARCHAR2,
p_task_id OUT NUMBER, p_task_type OUT VARCHAR2, p_task_docbase OUT VARCHAR2, p_task_result OUT VARCHAR2, p_task_request_xml OUT CLOB, p_task_result_xml OUT CLOB);

p_task_types is defined as follows :

create or replace
TYPE T_TASK_TYPES IS TABLE OF VARCHAR2(20)


The procedure loops through some fields in the database and polls for some content updates ( don't get me started with active polling ;-) ) .

In my C# procedure I prepare the input parameters, for completness I'm pasting the C# code inside. I have left out the initialization stuff for the database connection.

// Input - parameters to Stored proc.
cmd.Parameters.Add("p_dispatcher_name", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
cmd.Parameters["p_dispatcher_name"].Value = this.DispatcherName;

// p_task_types

string[] str = new string[2] { "UNIT_TEST", "UNIT_TESTS" };

OracleParameter p_types = cmd.Parameters.Add("p_task_types", OracleDbType.Varchar2);
p_types.Direction = ParameterDirection.Input;
p_types.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_types.UdtTypeName = "T_TASK_TYPES";

p_types.Value = str;

p_types.ArrayBindSize = new int[2] { 20, 20} ;
p_types.Size = str.Length;


// task status : only one status can be given.
cmd.Parameters.Add("p_task_status", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
cmd.Parameters["p_task_status"].Value = TaskStatus.COMPLETED;

// sync - hard coded
cmd.Parameters.Add("p_task_async", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
cmd.Parameters["p_task_async"].Value = "SYNC";

// caller_id
cmd.Parameters.Add("p_caller_id", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
cmd.Parameters["p_caller_id"].Value = CallerId;


// Output-parameters for stored procedure

// classic int32 and long-error ??
cmd.Parameters.Add("p_task_id", OracleDbType.Int32).Direction = ParameterDirection.Output;
cmd.Parameters.Add("p_task_type", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

cmd.Parameters.Add("p_task_docbase", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
cmd.Parameters.Add("p_task_result", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

cmd.Parameters.Add("p_task_request_xml", OracleDbType.Clob).Direction = ParameterDirection.Output;
cmd.Parameters.Add("p_task_result_xml", OracleDbType.Clob).Direction = ParameterDirection.Output;
// "done"

// execute
cmd.ExecuteNonQuery();


So far, when executing the command, I get a PL-SQL error:

ORA-06550: Zeile 1, Spalte 7:
PLS-00306: wrong number or types of arguments in call to 'POLL_DISPATCHER_TASK2'
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored

So far I have identified, that this error occurs on the parameter passing for the p_task_types - field.
As I understood, there should be passed an Varchar-array ( string-array ) to the procedure.
So we have tried multiple variants of that code :

string[] str = new string[2] { "UNIT_TEST", "UNIT_TESTS" };

OracleParameter p_types = cmd.Parameters.Add("p_task_types", OracleDbType.Varchar2);
p_types.Direction = ParameterDirection.Input;
p_types.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_types.UdtTypeName = "T_TASK_TYPES";

p_types.Value = str;

p_types.ArrayBindSize = new int[2] { 20, 20} ;
p_types.Size = str.Length;

Trying it with OracleCollectionType.None instead PLSQLAssociativeArray, an other OracleDBType, with and without bind sizes or UdtTypeName, with no effect, always the same error message.

For now with the existing tutorials I am at the end of my ideas, the problem seems to be somewhere in the construction for the parameter passing to the stored procedure, as it works fine called from Java.

Does anyone have an idea where the error/problem is as I have used for now about 5 hours trying to figure this out and somehow, I have no more ideas ;-) .

Thanks alot for your help !

regards

roland