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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
fad
Posts: 6
Joined: Wed 08 Oct 2014 13:08

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

Post by 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.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by 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;

Post Reply