Calling oracle package function with table of varc

问题:

I am calling a oracle package function from c#.net but facing below issue

Error : ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'MYFUNCTION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My function is :

function Myfunction(lines in t_line, brokerSeq in number, 
                    broker_code_in_file in varchar2, 
                    pb_pv_ind in varchar2)

First parameter is declared in same package like

type t_line is table of varchar2(100) index by binary_integer;

Please assist.

回答1:

I have an example, which is in VB.NET, but I guess you can translate it to C#:

Public Function GetObjects(ByVal lines As String()) As DataTable
   Dim cmd As OracleCommand
   Dim par As OracleParameter
   Dim da As OracleDataAdapter, dt As New DataTable

   cmd = New OracleCommand("BEGIN :res := Myfunction(:lines); END;", server.con)
   cmd.CommandType = CommandType.Text

   ' Return value
   cmd.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue)

   par = cmd.Parameters.Add("lines", OracleDbType.Varchar2, ParameterDirection.Input)
   par.CollectionType = OracleCollectionType.PLSQLAssociativeArray
   par.Value = lines
   par.Size = lines.Length
   cmd.ExecuteNonQuery()

   da = New OracleDataAdapter(cmd)
   da.Fill(dt)
   Return dt

End Function