DbProviderFactory, Oracle and a ref cursor

  • Thread starter Thread starter Paul Lockwood
  • Start date Start date
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
 
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?
 
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.
 
Back
Top