Executing overloaded Oracle functions

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Black Moon
Posts: 4
Joined: Tue 03 Mar 2015 20:07

Executing overloaded Oracle functions

Post by Black Moon » Wed 04 Mar 2015 08:40

Hi All!

I have are 2 overloaded functions in oracle package.

First: -

Code: Select all

function change( 
    pNo in number, 
    pcode in VARCHAR2, 
    pname in VARCHAR2, 
    pdescription in VARCHAR2, 
    pref$object_no in NUMBER)  return number 
Second: -

Code: Select all

function change( 
    pRowId in rowid, 
    pcode in VARCHAR2, 
    pname in VARCHAR2, 
    pdescription in VARCHAR2, 
    pref$object_no in NUMBER)  return number
And try to execute them from client via Devart.Data driver.

Code: Select all

OracleParameter [] funcParams = 
        {
            new OracleParameter("pNo", 12238643),
            new OracleParameter("pname", "2238643"),
            new OracleParameter("pcode", "11111111111111"),
            new OracleParameter("pdescription", "11111111111111"),
            new OracleParameter("pref$object_no", 0),
            new OracleParameter() { ParameterName = "result", Direction = ParameterDirection.ReturnValue }
        };

        using (OracleCommand cmd = new OracleCommand("obj$group_service.change", Entry.Connection) { CommandType = CommandType.StoredProcedure, PassParametersByName = true })
        {
            cmd.Parameters.AddRange(funcParams.ToArray());
            cmd.ExecuteNonQuery();
            result = Convert.ToInt64(cmd.Parameters["result"].Value);
        }
Devart Oraclecommand always generates SQL with second Oracle function signature, f.e

Code: Select all

begin
  -- Call the function
  :result := obj$group_service.change(prowid => :prowid,
                                      pcode => :pcode,
                                      pname => :pname,
                                      pdescription => :pdescription,
                                      pref$object_no => :pref$object_no);
end;
neverless my specification.

Black Moon
Posts: 4
Joined: Tue 03 Mar 2015 20:07

Re: Executing overloaded Oracle functions

Post by Black Moon » Wed 04 Mar 2015 08:50

But If I call

Code: Select all

cmd.ExecuteReader();
instead of

Code: Select all

cmd.ExecuteNonQuery();
I have 2 explicit queryes:

Code: Select all

	select PIPELINED from sys.user_procedures where object_name = :packname and procedure_name = :procname and procedure_name is null

Code: Select all

	select PIPELINED from sys.user_procedures where object_name = :packname and procedure_name = :procname
Task was solved by using
CommandType = Text
and including SQL-text into
BEGIN/END
tags.

But such behaviour is strange.
Any explanation will be pleased..
Thanks.

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

Re: Executing overloaded Oracle functions

Post by Pinturiccio » Fri 06 Mar 2015 13:44

We are investigating the issue and will post here about the results as soon as possible.

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

Re: Executing overloaded Oracle functions

Post by Pinturiccio » Wed 11 Mar 2015 11:08

We have reproduced and fixed the bug with redundant describing of stored procedures in OracleCommand with CommandType.StoredProcedure and with parameters, containing the '$' characters in their names. This will solve the issue you have encountered. Additionally with the new build, include "Describe Stored Procedure=False;" in your connection string to avoid describing in similar cases.

We will post here when the corresponding build of dotConnect for Oracle is available for download.

Post Reply