Executing overloaded Oracle functions

[Black Moon]

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]

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]

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

[Pinturiccio]

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.