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

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

Postby fad » Tue 23 Dec 2014 14:42

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.
fad
 
Posts: 6
Joined: Wed 08 Oct 2014 13:08

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

Postby Pinturiccio » Thu 25 Dec 2014 15:30

fad wrote:{"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;
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle