Page 1 of 1
Function whcih returns array
Posted: Mon 27 Sep 2010 12:49
by maxcpr
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]
Posted: Tue 28 Sep 2010 18:29
by Shalex
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();
}
Posted: Wed 29 Sep 2010 11:46
by maxcpr
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.
Posted: Thu 30 Sep 2010 13:53
by Shalex
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.
Posted: Mon 04 Oct 2010 15:25
by Shalex
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).
Posted: Thu 14 Oct 2010 16:00
by Shalex
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 .