Function whcih returns array

Function whcih returns array

Postby 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]
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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();
            }
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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.
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle