Call a package function from C#: PLS-00306

Question

Hi,

I'm trying to call an Oracle package function from C#, but I keep getting the same error: PLS-00306: wrong number or types of arguments in call to 'F_CTCPREPAREP151HEADER'.

This is the function declaration in the package:

function f_CtcPrepareP151Header
(
as_codjal in varchar2/*,
ad_datdoc in varchar2,
as_codfnr in varchar2,
ad_dtfafo in varchar2,
as_reffnr in varchar2,
as_vcs in varchar2,
as_devfnr in varchar2,
ad_datech in varchar2,
al_totdev in number,
al_tvadev in number,
al_tvafac in number,
as_codtva in varchar2,
as_libele in varchar2,
as_xlogcr in varchar2,
as_typesc in varchar2,
al_pouesc in number,
al_jouesc in number,
al_docrec in number,
as_msglan in varchar2*/,
rs_msgerr in out varchar2
)
return integer;

This is the function declaration in the package body:

function f_CtcPrepareP151Header
(
as_codjal in varchar2/*,
ad_datdoc in varchar2,
as_codfnr in varchar2,
ad_dtfafo in varchar2,
as_reffnr in varchar2,
as_vcs in varchar2,
as_devfnr in varchar2,
ad_datech in varchar2,
al_totdev in number,
al_tvadev in number,
al_tvafac in number,
as_codtva in varchar2, -- tvaspe
as_libele in varchar2, -- numero de bon de commande (texte libre)
as_xlogcr in varchar2,
as_typesc in varchar2,
al_pouesc in number,
al_jouesc in number,
al_docrec in number, -- numero de document Recomatics (ctcd15.des001)
as_msglan in varchar2*/,
rs_msgerr in out varchar2
)
return integer
is
begin
/*rs_msgerr := 'test';*/
return 1;
end f_CtcPrepareP151Header;

This is the way I call the function:

OleDbCommand l_oCmd1 = new OleDbCommand("PKG_TEMREC.f_ctcPrepareP151Header", l_oConContractor);
l_oCmd1.CommandType = CommandType.StoredProcedure;

l_strExtraInfo = "f_ctcPrepareP151Header : Create return parameter";
OleDbParameter l_RetValue1 = l_oCmd1.Parameters.Add("RetVal", OleDbType.Integer);
l_RetValue1.Direction = ParameterDirection.ReturnValue;

l_strExtraInfo = "f_ctcPrepareP151Header : Create journal parameter";
OleDbParameter l_oJournalPar1 = new OleDbParameter("as_codjal", OleDbType.VarChar);
l_oJournalPar1.Direction = ParameterDirection.Input;
l_oJournalPar1.Value = l_strJournal;
l_oJournalPar1.Size = l_strJournal.Length;
l_oCmd1.Parameters.Add(l_oJournalPar1);

l_strExtraInfo = "f_ctcPrepareP151Header : Create message parameter";
OleDbParameter l_oErrMsgPar1 = l_oCmd1.Parameters.Add("rs_msgerr", OleDbType.VarChar);
l_oErrMsgPar1.Direction = ParameterDirection.InputOutput;
l_oErrMsgPar1.Size = 4000;
l_oErrMsgPar1.Value = new String(' ', 4000);

try
{
ExecuteFunction(l_oCmd1, l_oConContractor, l_oTrans, l_RetValue1, l_oErrMsgPar1, "f_ctcPrepareP151Header");
}
catch (Exception e)
{
throw new Exception("Error while preparing invoice header: " + e.Message);
}

The function ExecuteFunction is the following:

private void ExecuteFunction(OleDbCommand oCmd, OleDbConnection oConn, OleDbTransaction oTrans, OleDbParameter oRetValue, OleDbParameter oErrMsg, string strFunctionName)
{
try
{
oCmd.Transaction = oTrans;
oCmd.ExecuteNonQuery();

if ((oRetValue != null) && (oRetValue.Value != null))
{
int iOutputValue = Convert.ToInt32(oRetValue.Value.ToString());
if (iOutputValue < 0)
{
throw new Exception(oErrMsg.Value.ToString());
}
}
}
catch (Exception e)
{
string l_strMsg = string.Format("Exception in procedure PKG_TEMREC." + strFunctionName + " - " + e.Message + "(" + oCmd.CommandText + ")");
throw new Exception(l_strMsg);
}
}

Can anybody tell me what I am doing wrong? I know that I have commented out a lot of parameters in the function declaration, but this is for testing purposes.

Thanks,

Kristof.

Answer 1

Maybe it is the returnvalue parameter? Maybe OleDbType.Integer is to small to for an Oracle integer. An Oracle integer can be very long. Much longer than 32 bits or 64 bits can hold.