Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
-
maxcpr
- Posts: 33
- Joined: Wed 10 Dec 2008 14:46
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).