1
Reply

[ DBMS_SQL.varchar2_table] How to define a output parameter in C# which maps to a PL/SQL table in oracle?

omesh pandita

omesh pandita

Aug 19 2008 6:26 AM
8.9k
here is what i did...

 OracleConnection objconnection = new  OracleConnection(connectionstring);
        OracleCommand objcommand = objconnection.CreateCommand();
        objcommand.Connection = objconnection;
        objcommand.CommandText = "UBE_XSITE_PKG_V2.SP_DATAUPLOAD";
        objcommand.CommandType = CommandType.StoredProcedure;

        ListBox1.SelectedIndex = 0;


        OracleParameter prm1 = new OracleParameter("PI_LOT", OracleDbType.Varchar2);
        prm1.Direction = ParameterDirection.Input;
        prm1.Value =  ListBox1.SelectedValue.ToString();
        objcommand.Parameters.Add(prm1);

        OracleParameter prm2 = new OracleParameter("PI_WS_OP", OracleDbType.Int32);
        prm2.Direction = ParameterDirection.Input;
        prm2.Value = (float)Int32.Parse(TextBox3.Text);
        objcommand.Parameters.Add(prm2);

        OracleParameter prm3 = new OracleParameter("PI_SOURCE_SITE", OracleDbType.Varchar2);
        prm3.Direction = ParameterDirection.Input;
        prm3.Value = DropDownList1.SelectedValue.ToString();
        objcommand.Parameters.Add(prm3);

        OracleParameter prm4 = new OracleParameter("PI_WS_FLAG", OracleDbType.Varchar2);
        prm4.Direction = ParameterDirection.Input;
        prm4.Value = DropDownList5.SelectedValue.ToString();
        objcommand.Parameters.Add(prm4);

        OracleParameter prm5 = new OracleParameter("PI_WS_QTY", OracleDbType.Int32);
        prm5.Direction = ParameterDirection.Input;
        prm5.Value = (float)Int32.Parse(TextBox2.Text);
        objcommand.Parameters.Add(prm5);

        OracleParameter prm6 = new OracleParameter("PO_EXCEPTIONS", OracleDbType.Varchar2);
        prm6.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        prm6.Direction = ParameterDirection.Output;
        prm6.IsNullable = true;
        prm6.Size = 4000;
        objcommand.Parameters.Add(prm6);

        objconnection.Open();
       objcommand.ExecuteNonQuery();
        objconnection.Close();



getting error arraybindsize not valid trying setting that but of no use.....

Answers (1)