Executing overloaded Oracle functions

Executing overloaded Oracle functions

Postby 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

Postby 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.
Black Moon
 
Posts: 4
Joined: Tue 03 Mar 2015 20:07

Re: Executing overloaded Oracle functions

Postby 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: 1953
Joined: Wed 02 Nov 2011 09:44

Re: Executing overloaded Oracle functions

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle