Procedure傳入array參數範例 in Oracle

以下為Procedure傳入array參數範例：
Procedure部份

create or replace PACKAGE  PKG_TEST AS
TYPE NUMBER_ARRAY IS TABLE OF number INDEX BY BINARY_INTEGER;
TYPE STRING_ARRAY IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;

 procedure GET_ORDERLIST
 (
  P_ORDERLISTID in PKG_TEST.STRING_ARRAY,
  P_RETURNCURSOR OUT SYS_REFCURSOR
 );

/

 procedure GET_ORDERLIST
 (
   P_ORDERLISTID in PKG_TEST.STRING_ARRAY,
   P_RETURNCURSOR OUT SYS_REFCURSOR
 ) as
    varSQL varchar(4000) := '';
    var_OrderIds string(4000) := '''0''';
   begin   
  
  for i in P_ORDERLISTID.first .. P_ORDERLISTID.last loop
   var_OrderIds := var_OrderIds || ',''' ||  P_ORDERLISTID(i) || '''';
  end loop;

  varSQL := 'select * from Order where orderid  in (' || var_OrderIds || ')';

  DBMS_OUTPUT.PUT_LINE(varSQL);
  OPEN P_RETURNCURSOR FOR varSQL;

 end GET_ORDERLIST;

end PKG_TEST

/

C#程式部份

public List GetOrderList(List theSearchList)
{
 var oracleCommand = new OracleCommand();
 oracleCommand.Connection = (OracleConnection)command.Connection;
 oracleCommand.CommandType = CommandType.StoredProcedure;
 oracleCommand.CommandText = "PKG_TEST.GET_ORDERLIST";

 var arryIds = new OracleParameter
 {
  ParameterName = "@P_ORDERLISTID",
  OracleDbType = OracleDbType.Varchar2,
  CollectionType = OracleCollectionType.PLSQLAssociativeArray,
  Value = theSearchList.ToArray(),
  Size = theSearchList.Count(),
  Direction = ParameterDirection.Input
 };
 oracleCommand.Parameters.Add(arryIds);
 oracleCommand.Parameters.Add("@P_RETURNCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
 oracleCommand.ExecuteNonQuery();

 var dataReader = ((OracleRefCursor)oracleCommand.Parameters["@P_RETURNCURSOR"].Value).GetDataReader();

 var ListDto = new List();

 while (dataReader.Read())
 {
  var dto = new MollyBetOrderBetDto();
  if (!dataReader["orderid"].Equals(DBNull.Value)) dto.OrderID = dataReader["orderid"].ToString();
  if (!dataReader["price"].Equals(DBNull.Value)) dto.Price = Convert.ToDecimal(dataReader["price"]);
  ListDto.Add(dto);
 }

 #region CloseAndDisposeReaderAndCommand
 if (!dataReader.IsNull())
 {
  dataReader.Close();
  dataReader.Dispose();
 }
 oracleCommand.Connection.Close();
 oracleCommand.Connection.Dispose();
 if (!oracleCommand.IsNull())
  oracleCommand.Dispose();
 #endregion
 
 return ListDto;
}

遇到如果需要傳入空array可參考

https://community.oracle.com/message/4126678#4126678

https://community.oracle.com/thread/1000596

http://docs.oracle.com/html/E15167_01/OracleParameterClass.htm#i1012269