Data access via stored procedure - return type as PL/SQL Array

Question

User1510197497 posted

I get an error as array bind type must match pl/sqldata type when i execute a stored proc.
My stored proc looks like this:

PROCEDURE Datasearch
(p_type in varchar2,
p_empid in varchar2,
p_empcode out tempid,
p_lname out tlname,
p_fname out tfname,
p_nickname out tnickname,
p_position out tposition,
p_startdt out tstartdt,
p_status out tstatus,
p_loastart out tloastart,
p_loaend out tloaend,
p_xchange out txchange,
p_trx out ttrx,
p_enddt out tenddt,
p_hfind out thfind)
is
... end Empsearch

And I am trying to fire this stored proc using the following C# code:

/////////////////////////////////////////////////////////////////////////////////////////////////////////

OracleConnection con = new OracleConnection();
con.ConnectionString = "ValidConnectionString";
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);

string cmdTxt = "begin MyPack.Datasearch(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15); end;";
OracleCommand myCMD = new OracleCommand(cmdTxt, con);

//Input params
myCMD.Parameters.Add("1", OracleDbType.Varchar2).Value = "All";
myCMD.Parameters.Add("2", OracleDbType.Varchar2).Value = "1129";
myCMD.Parameters[0].Direction = ParameterDirection.Input;
myCMD.Parameters[1].Direction = ParameterDirection.Input;

//Output params
OracleParameter Param3 = myCMD.Parameters.Add("3", OracleDbType.Int16);
OracleParameter Param4 = myCMD.Parameters.Add("4", OracleDbType.Varchar2);
OracleParameter Param5 = myCMD.Parameters.Add("5", OracleDbType.Varchar2);
OracleParameter Param6 = myCMD.Parameters.Add("6", OracleDbType.Varchar2);
OracleParameter Param7 = myCMD.Parameters.Add("7", OracleDbType.Varchar2);
OracleParameter Param8 = myCMD.Parameters.Add("8", OracleDbType.Date);
OracleParameter Param9 = myCMD.Parameters.Add("9", OracleDbType.Varchar2);
OracleParameter Param10 = myCMD.Parameters.Add("10", OracleDbType.Varchar2);
OracleParameter Param11 = myCMD.Parameters.Add("11", OracleDbType.Varchar2);
OracleParameter Param12 = myCMD.Parameters.Add("12", OracleDbType.Varchar2);
OracleParameter Param13 = myCMD.Parameters.Add("13", OracleDbType.Varchar2);
OracleParameter Param14 = myCMD.Parameters.Add("14", OracleDbType.Date);
OracleParameter Param15 = myCMD.Parameters.Add("15", OracleDbType.Varchar2);

//test ends

for (int i = 2; i < 15; i++)
{
myCMD.Parameters.Direction = ParameterDirection.Output;
myCMD.Parameters.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
myCMD.Parameters.Value = null;
myCMD.Parameters.Size = 3;
myCMD.Parameters.ArrayBindSize = new int[3] { 20, 20, 20 };

}

myCMD.ExecuteNonQuery();

/////////////////////////////////////////////////////////////////////////////////////////////////////////

I get an error on the above ExecuteNonQuery statement..

Any help/pointer will be highly appreciated.

-Cheers

Answer 1

User269602965 posted

myCMD.Parameters.Clear();
myCMD.Parameters.Add("1", OracleDbType.Varchar2,  "All", ParameterDirection.Input);
myCMD.Parameters.Add("2", OracleDbType.Varchar2, "1129", ParameterDirection.Input);
myCMD.Parameters.Add("3", OracleDbType.Int16, Param3, ParameterDirection.Output);
myCMD.Parameters.Add("4", OracleDbType.Varchar2, Param4, ParameterDirection.Output);
etc.

Answer 2

User1510197497 posted

Hi Lanie,

Even after clearing the parameters before adding, I get the same excpetion:

ORA-06550: line 1, column 72:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 82:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Is this got to do anything with the return data type mismatch.. i read in another forum abt a diff issue but DB was throwing this error...

-Cheers

Answer 3

User269602965 posted

Could be

We have noticed here on this forum instances of data type problems with ODP.NET and VB.NET/CS.NET

Like in Oracle database it is NUMBER(12,0) (an integer) and you think INT32 would work, but fails, and then DECIMAL does work.

Answer 4

User1510197497 posted

Hi,

I changed the datatype from int32 to decimal.

But its not working .

Please suggest me how to resolve this.