Calling Stored procedures from C# with Oracle type like Table of CustomType
i've got an issue in c# executing an Oracle Stored Procedure using Oracle.DataAccess library.
In oracle I've got this structure;
CREATE OR REPLACE TYPE O_MOV_LST_STA_STATUS AS TABLE OF O_MOV_STATION_STATUS;o_mov_lst_sta_status
And the type description is as follows:
CREATE OR REPLACE TYPE O_MOV_STATION_STATUS AS OBJECT
(
StationT NUMBER(10),
StationN NUMBER(10),
Stat NUMBER(3),
Loaded NUMBER(1),
Capacity NUMBER(10),
CurrentCount NUMBER(10),
Box NUMBER(10)
);
And the procedure definition:
PROCEDURE UPDATE_STATIONS(pn_warehouse_id_in IN wms_warehouse.warehouse_id%TYPE,
prl_sta_status_in IN o_mov_lst_sta_status,
pn_error_code_out OUT NUMBER,
pv_error_text_out OUT NOCOPY VARCHAR2);
In C# the code is like this:
public struct O_Mov_Station_Status
{
public decimal StationT;// NUMBER(10),
public decimal StationN;// NUMBER(10),
public decimal Stat;// NUMBER(3),
public decimal Loaded;// NUMBER(1),
public decimal Capacity;// NUMBER(10),
public decimal CurrentCount;// NUMBER(10),
public decimal Box;// NUMBER(10)
}
public struct Lst_O_Mov_Station_Status
{
public IList<O_Mov_Station_Status> Lst_Station_Status;
}
... Initializing vars and objects ...
OracleCommand cmd = new OracleCommand("MyPackage.UPDATE_STATIONS", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Oracle Parameter
OracleParameter objParam = new OracleParameter();
objParam = new OracleParameter("PN_WAREHOUSE_ID_IN", OracleDbType.Decimal);
objParam.Direction = System.Data.ParameterDirection.Input;
objParam.Value = 20000;
cmd.Parameters.Add(objParam);
objParam.Dispose();
objParam = new OracleParameter("prl_sta_status_in", OracleDbType.Object);
objParam.OracleDbType = OracleDbType.Object;
objParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
objParam.Direction = System.Data.ParameterDirection.Input;
objParam.UdtTypeName = "O_MOV_STATION_STATUS";
objParam.Value = lSS;
objParam.DbType = System.Data.DbType.Object;
cmd.Parameters.Add(objParam);
objParam.Dispose();
objParam = new OracleParameter("PN_ERROR_CODE_OUT", OracleDbType.Decimal);
objParam.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(objParam);
objParam.Dispose();
objParam = new OracleParameter("PV_ERROR_TEXT_OUT", OracleDbType.Varchar2);
objParam.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(objParam);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Once I execute the ExecuteNonQuery() I obtain this Error: OracleParameter.Value is not valid.
Everyone know how can I send this list as a Parameter of the stored procedure?
Notice that the list I need is a Table of Custom Type in Oracle
Thank you so much.
Regards.
OracleCollectionType.PLSQLAssociativeArray means PL/SQL Associative Array of a plain type eg
CREATE OR REPLACE TYPE O_MOV_STATION_STATUS AS TABLE OF NUMBER INDEX BY INTEGER;
Complex object types are not possible as far as I know. I assume you have to do a loop over each record of your table or write a function in PL/SQL to wrap the object.
链接地址: http://www.djcxy.com/p/20292.html