DbProviderFactory, Oracle and a ref cursor

P

Paul Lockwood

Amazingly few hours on Google has not resolved this seeming simple
issue:

Using the new ADO.Net 2.0 provider model we are trying to read data
from an Oracle sproc which is similar to this:


CREATE OR REPLACE PROCEDURE P_VISITORS_SELECT
(
outCursor OUT sys_refcursor
)
IS
BEGIN
OPEN outCursor FOR
SELECT ID
FROM VISITORS
WHERE ID = 252
;
END;



The problem is letting the provider know that the output is a ref
cursor. I have included simplified code below and would rather see the
following:
param2.DbType = DbType.Cursor; // NOTICE THE WORD CURSOR HERE
instead of
param2.DbType = DbType.Object;
But, DbType does not provide an option for Cursor. The Provider model
surely support Oracle stored procedures (?), so can anyone point me in
the right direction?



DbProviderFactory dbProviderFactory =
System.Data.Common.DbProviderFactories.GetFactory("System.Data.OracleClient");
using (DbConnection connection =
dbProviderFactory.CreateConnection())
{
connection.ConnectionString = "Data Source=******;User
Id=*****;Password=*****";

System.Data.Common.DbCommand command =
dbProviderFactory.CreateCommand();
System.Data.Common.DbCommandBuilder commandBuilder =
dbProviderFactory.CreateCommandBuilder();
command.CommandText = "P_VISITORS_SELECT";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;

System.Data.Common.DbDataAdapter dataAdapter =
dbProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = command;

{
DbParameter param2 =
dbProviderFactory.CreateParameter();
param2.Direction = ParameterDirection.Output;
param2.ParameterName = "outCursor";
param2.DbType = DbType.Object;
command.Parameters.Add(param2);
}

DataSet tempDataSet = new DataSet();
dataAdapter.Fill(tempDataSet);
}

As-is the code bombs with the error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'P_VISITORS_SELECT'
i.e. it needs to know that the parameter a ref cursor
 
P

Paul Lockwood

Bump.

Do any of you brainy MVPs know if the generic ADO.Net 2.0 provider
model supports returning SELECT data from an Oracle stored procedure?
 
P

Paul Lockwood

Sujith,

Thanks for trying to help, but I think OracleDbType.RefCursor is from
the v1.1 of the ODP provider. We are trying to read an oracle sproc via
the new Microsoft ADO.Net 2.0 generic provider interface.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top