Calling package function (and using it's return value)

[fad]

I'm trying to call package function and retrieve the return value, but I receive always the following error: {"ORA-06550: riga 2, colonna 3:\nPLS-00221: 'UPDATESTATUS' non è una procedura oppure non è definita\nORA-06550: riga 2, colonna 3:\nPL/SQL: Statement ignored"}

Package function:

Code: Select all

function updateStatus(par1 mytab.myField1%type, -- Number(10)
                      par2 mytab.myField2%type, -- Number(10)
                      par3 integer) 
return boolean;   

C# Code:

Code: Select all

try
{
   OraclePackage pkg = new OraclePackage();
   pkg.Connection = m_session.getSession();
   pkg.PackageName = "MYPKG";

   OracleParameterCollection pars = new OracleParameterCollection();
   pars.Add("par1", myVal1);
   pars.Add("par2", System.DBNull.Value);
   pars.Add("par3", (int)myVal3);

   object ret = pkg.ExecuteProcedure("updateStatus", pars);
}
catch (Exception ex)
{
   Console.log(ex.Message);
}

If I convert the function in a procedure (in oracle package), all works well..
Any idea..?

Best regars,
Fad.

[Pinturiccio]

{"ORA-06550: riga 2, colonna 3:\nPLS-00221: 'UPDATESTATUS' non e una procedura oppure non e definita\nORA-06550: riga 2, colonna 3:\nPL/SQL: Statement ignored"}

The given error text does not display its initial reason. Unfortunately, such error is returned by the server, and we display the text, that was sent by Oracle.

The reason of this exception is that the parameters collection does not include parameter for the returned value of your function. To fix the issue, add one more parameter to your code:

Code: Select all

pars.Add("RESULTS", OracleDbType.Boolean);
pars["RESULTS"].Direction = ParameterDirection.ReturnValue;