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

  • 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

    Friday, January 6, 2012 12:55 PM

All replies

  • 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.

    Friday, January 6, 2012 6:25 PM
  • 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
    Saturday, January 7, 2012 6:08 AM
  • 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.

    Saturday, January 7, 2012 1:29 PM
  • User1510197497 posted

    Hi,

    I changed the datatype from int32 to decimal.

    But its not working .

    Please suggest me how to resolve this.

    Sunday, January 8, 2012 3:38 AM