Function whcih returns array

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Function whcih returns array

Post by maxcpr » Mon 27 Sep 2010 12:49

Hi!

I have a function which returns array :

Code: Select all

 Devart.Data.Oracle.OracleCommand command = new ...

fun.Name = "NB.GetTicketMain";

param = new Devart.Data.Oracle.OracleParameter(null, Devart.Data.Oracle.OracleDbType.Table) { 
                ObjectTypeName = "TypeName", 
                Direction = System.Data.ParameterDirection.ReturnValue,
 "RETURN" });

fun.ExecuteReader();

It has been working fine till i installed new version dotConnect (6.0). Now the same code fails :
ORA-01036: wrong name/number of variable

Where is problem here?

Thanks[/code]

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 28 Sep 2010 18:29

I cannot reproduce the error with the following sample. Please modify it so that we can reproduce it in our environment.
script

Code: Select all

CREATE OR REPLACE TYPE TEmployees AS 
  TABLE OF VARCHAR2(40);

CREATE TABLE CompanyEmpObject (
  Code NUMBER PRIMARY KEY,
  Company VARCHAR2(40),
  Employees TEmployees    
)NESTED TABLE Employees STORE AS N_TAB;

INSERT INTO CompanyEmpObject
  (Code, Company, Employees)
VALUES
  (1, 'Microsoft', TEmployees('PROGRAMMER', 'MANAGER', 'ELECTRICIAN', 'CLEANUP MEN'));

CREATE OR REPLACE PACKAGE SCOTT.PACK AS
  FUNCTION getOracleTable RETURN TEmployees;
END PACK;

/

CREATE OR REPLACE PACKAGE BODY SCOTT.PACK 
IS 
  FUNCTION getOracleTable RETURN TEmployees AS
   ret TEmployees;
  BEGIN
    SELECT Employees INTO ret FROM CompanyEmpObject;
    RETURN ret;
  END;
END PACK;
/
C#

Code: Select all

            using (OracleConnection conn = new OracleConnection()){
            conn.ConnectionString = connStr;

                OracleCommand command = conn.CreateCommand();
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "PACK.getOracleTable";

                OracleParameter param = new Devart.Data.Oracle.OracleParameter();
                param.ParameterName = "ret";
                param.OracleDbType = OracleDbType.Table;
                param.ObjectTypeName = "SCOTT.TEmployees";
                param.Direction = System.Data.ParameterDirection.ReturnValue;

                command.Parameters.Add(param);
                conn.Open();
                command.ExecuteNonQuery(); 
            }

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Wed 29 Sep 2010 11:46

Hi!

I've found the bug/feature.
The diffrence here between my code and yours is :

fun.ExecuteReader() vs fun.ExecuteNoneQuery().

In older version ExecuteReader() worked but now it fails.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 30 Sep 2010 13:53

If a command doesn't return any result set, we recommend using ExecuteNonQuery() - its performance is much better than the one of ExecuteReader(). We will investigate the difference in behaviour of ExecuteReader() in the 5.35.79 and 5.70.170 versions and notify you about the results. Also it is possible to use ExecuteReader() in the sample above if not to set parameter manually but use OracleCommand.ParameterCheck=true.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 04 Oct 2010 15:25

The problem with ExecuteReader()/ExecuteScalar() is solved. The fix will be available in the next build. I will post here when it is available. As an additional workaround, please rename the ReturnValue parameter to "RESULT" (in this case our internal implementation in the current build does not create extra parameter).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 14 Oct 2010 16:00

New build of dotConnect for Oracle 5.70.180 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19236 .

Post Reply