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_CTCPREPAREP1 51HEADER'.

This is the function declaration in the package:
function f_CtcPrepareP15 1Header
(
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_CtcPrepareP15 1Header
(
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_CtcPrepareP15 1Header;

This is the way I call the function:
OleDbCommand l_oCmd1 = new OleDbCommand("P KG_TEMREC.f_ctc PrepareP151Head er", l_oConContracto r);
l_oCmd1.Command Type = CommandType.Sto redProcedure;

l_strExtraInfo = "f_ctcPrepareP1 51Header : Create return parameter";
OleDbParameter l_RetValue1 = l_oCmd1.Paramet ers.Add("RetVal ", OleDbType.Integ er);
l_RetValue1.Dir ection = ParameterDirect ion.ReturnValue ;

l_strExtraInfo = "f_ctcPrepareP1 51Header : Create journal parameter";
OleDbParameter l_oJournalPar1 = new OleDbParameter( "as_codjal" , OleDbType.VarCh ar);
l_oJournalPar1. Direction = ParameterDirect ion.Input;
l_oJournalPar1. Value = l_strJournal;
l_oJournalPar1. Size = l_strJournal.Le ngth;
l_oCmd1.Paramet ers.Add(l_oJour nalPar1);

l_strExtraInfo = "f_ctcPrepareP1 51Header : Create message parameter";
OleDbParameter l_oErrMsgPar1 = l_oCmd1.Paramet ers.Add("rs_msg err", OleDbType.VarCh ar);
l_oErrMsgPar1.D irection = ParameterDirect ion.InputOutput ;
l_oErrMsgPar1.S ize = 4000;
l_oErrMsgPar1.V alue = new String(' ', 4000);

try
{
ExecuteFunction (l_oCmd1, l_oConContracto r, l_oTrans, l_RetValue1, l_oErrMsgPar1, "f_ctcPrepareP1 51Header");
}
catch (Exception e)
{
throw new Exception("Erro r while preparing invoice header: " + e.Message);
}

The function ExecuteFunction is the following:
private void ExecuteFunction (OleDbCommand oCmd, OleDbConnection oConn, OleDbTransactio n oTrans, OleDbParameter oRetValue, OleDbParameter oErrMsg, string strFunctionName )
{
try
{
oCmd.Transactio n = oTrans;
oCmd.ExecuteNon Query();

if ((oRetValue != null) && (oRetValue.Valu e != null))
{
int iOutputValue = Convert.ToInt32 (oRetValue.Valu e.ToString());
if (iOutputValue < 0)
{
throw new Exception(oErrM sg.Value.ToStri ng());
}
}
}
catch (Exception e)
{
string l_strMsg = string.Format(" Exception in procedure PKG_TEMREC." + strFunctionName + " - " + e.Message + "(" + oCmd.CommandTex t + ")");
throw new Exception(l_str Msg);
}
}

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?