Call a package function from C#: PLS-00306

[Kristofds]

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.

[debasisdas]

why are you trying to execute a function ? simply use it in SQL statement and pass the list of parameters.

[Kristofds]

Then how about the in/out parameter?