ErodeYogesh Ответов: 0

Как передать массив в функцию Oracle из C#?


CREATE TABLE BUSINESS ( BE_ID VARCHAR2(50 ) NOT NULL , 
                             ID VARCHAR2(50 ) NOT NULL , 
                             DESCRIPTION VARCHAR2(4000 ) ) ;


create or replace Package HCL0100A0 as

Type PARR is table of business%rowtype index by binary_integer;

Function DML_business_category( p_inputs in PARR,
                            p_dml_indicator in        varchar2,
                            p_error_code    in   out  varchar2,
                            p_error_msg     in   out  varchar2,
                            p_error_details in   out  varchar2
                           )
return varchar2;

End HCL0100A0;


Что я уже пробовал:

public OracleParameter[] PCL0501A0_ItemTypes(DataSet objDataset, string[] stringInfo, out string stringCommandText, out string stringStatus, out string[] stringOutputResult)
        {          
            OracleParameter[] objOracleParameter = new OracleParameter[objDataset.Tables[0].Rows.Count];
            OracleParameter[] objOracleParam = new OracleParameter[6];
            stringCommandText = "HCL0100A0.DML_business_category";

            objODBManager._oraclecmd = new OracleCommand();
            objODBManager._oraclecmd.CommandText = stringCommandText;
            objODBManager._oraclecmd.CommandType = CommandType.StoredProcedure;            
            objODBManager._oraclecmd.Connection = objODBManager.objOraclConnection;
            string stringInputValues = string.Empty;
            try
            {
                objOracleParam[1] = new OracleParameter("p_inputs", OracleDbType.Array);
                objOracleParam[1].Direction = ParameterDirection.Input;
                objOracleParam[1].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                objOracleParam[1].Value = new string[3] { objDataset.Tables[0].Rows[0]["BE_ID"].ToString(), objDataset.Tables[0].Rows[0]["ID"].ToString(), objDataset.Tables[0].Rows[0]["DESCRIPTION"].ToString() };                               

                objOracleParam[2] = new OracleParameter("p_dml_indicator", OracleDbType.Varchar2);
                objOracleParam[2].Value = "I";

                objOracleParam[3] = new OracleParameter("p_error_code", OracleDbType.Varchar2, 100);
                objOracleParam[3].Direction = ParameterDirection.InputOutput;
                objOracleParam[3].Value = stringErrorCode;

                objOracleParam[4] = new OracleParameter("p_error_msg", OracleDbType.Varchar2, 4000);
                objOracleParam[4].Direction = ParameterDirection.InputOutput;
                objOracleParam[4].Value = stringErrorMsg;

                objOracleParam[5] = new OracleParameter("p_error_details", OracleDbType.Varchar2, 4000);
                objOracleParam[5].Direction = ParameterDirection.InputOutput;
                objOracleParam[5].Value = stringErrorDetails;

                objOracleParam[0] = new OracleParameter("returnValue", OracleDbType.Varchar2, 255);
                objOracleParam[0].Direction = ParameterDirection.ReturnValue;

                stringStatus = "SUCCESS";
                stringOutputResult = null;
                return objOracleParam;
            }
            catch (Exception objException)
            {
                objCommonFunction.Exception(stringInfo, objException, out stringStatus, out stringOutputResult);
                objException = null;
                return null;
            }
            finally
            {
            }
        }

0 Ответов