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

Read more...

遇到的Oracle錯誤訊息筆記

錯誤訊息為：
--------------------------------------------------------------------------------------------------------------------------

ORA-00911:invalid character (字元無效)
--------------------------------------------------------------------------------------------------------------------------

00911. 00000 -  "invalid character"

*Cause:    identifiers may not start with any ASCII character other than

           letters and numbers.  $#_ are also allowed after the first

           character.  Identifiers enclosed by doublequotes may contain

           any character other than a doublequote.  Alternative quotes

           (q'#...#') cannot use spaces, tabs, or carriage returns as

           delimiters.  For all other contexts, consult the SQL Language

           Reference Manual.

查到的原因為我在串SQL字串裡，有加逗號，只要把逗號去掉即合;

例：

錯誤

varSQL := 'select * from log ;';

正確

varSQL :='select * from log';

--------------------------------------------------------------------------------------------------------------------------
PLS-00306: wrong number or types of arguments
--------------------------------------------------------------------------------------------------------------------------
最近在Call Procedure，有發生以下的問題，請注意程式給的Parameters參數是否跟Procedure裡面定義的一樣，有時候不小心沒注意到就會發生錯誤，這要注意一下。

錯誤訊息：
 System.Exception: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'ICS_USER_PAUSE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

例： SQL

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

程式

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
 };
 //Parameters一定要跟Procedure一樣才不會出錯喔
 oracleCommand.Parameters.Add(arryIds);   
 oracleCommand.Parameters.Add("@P_RETURNCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
 oracleCommand.ExecuteNonQuery();

--------------------------------------------------------------------------------------------------------------------------

ORA-06550:第1行,第7個欄位
--------------------------------------------------------------------------------------------------------------------------
PL/SQL: Statement ignored

原來是我把回傳的int型態給為回傳cursor型態，所以才會造成這樣的錯誤。

解決方法：

將cursor回傳型態改為int32型態即可。

另外也有可能是Store Procdeure沒有權限，所以也要檢查一下喔。

Read more...

Oracle decode用法

decode用法如下
select decode(3,1,'a',2,'b',3,'c','f') from dual;
結果為：c

與switch case概念相同
switch(3)
{
  case: 1
    console.write("a");
    break;
  case: 2
    console.write("b");
    break;
  case: 3
    console.write("c");
    break;
  default:
    console.write("f");
    break;
}

Read more...

Oracle SQL Developer新增欄位方法

新增欄位方法

Step1.在Table上按右鍵

Step2.新增按鍵，新增完成後按下ok即完成

Step3.若要提供Script給DBA, 可點選DDL可查看Script語法

Read more...

%TYPE Attribute 用法

在create Procedure 或是temp table時
新增的欄位型態可參考，已存在的欄位

範例：

create producre  spx_test
(
/*參考Event(table).EventID(Column)的欄位型態
      若Event.EventID型態為NUMBER(8,0)，則SPX的EventID就為eventID
 欄位型態會隨著Event.EventID而動態改戀
*/
  eventID in   Event.EventID%TYPE,
  Name in VRCHAR2(1000,BYTE)

)


參考來源：
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i6080

Read more...