从c#调用Oracle类型的存储过程，如Table of CustomType

Question

我在c#中使用Oracle执行Oracle存储过程时遇到了一个问题。DataAccess图书馆。

在oracle中我有这样的结构;

CREATE OR REPLACE TYPE O_MOV_LST_STA_STATUS AS TABLE OF O_MOV_STATION_STATUS;o_mov_lst_sta_status

类型描述如下:

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)
          );

和过程定义:

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);

在c#中，代码是这样的:

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(); 

一旦我执行ExecuteNonQuery()，我得到这个错误:OracleParameter。值无效

每个人都知道如何将这个列表作为存储过程的参数发送吗?

请注意，我需要的列表是一个Table of Custom Type in Oracle

谢谢你。

问候。

Answer 1

OracleCollectionType.PLSQLAssociativeArray表示纯类型的PL/SQL关联数组，例如

CREATE OR REPLACE TYPE O_MOV_STATION_STATUS AS TABLE OF NUMBER INDEX BY INTEGER;

据我所知，复杂的对象类型是不可能的。我假设您必须对表中的每条记录进行循环，或者在PL/SQL中编写一个函数来包装对象。