遇到的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沒有權限，所以也要檢查一下喔。