oledb challenges

Question

User702049738 posted

hello experts;

i tried including the connection string in my web config. I am using an OLEDB connection.

however I get the error message providerName doesn't exist and there is no ref cursor for oledb

I am trying to connect to an oracle database.

please see my code below

webconfig

<add name="myCtring"
connectionString="Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = xxxx.xxxxx.xxx))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxx)));User Id = xxx; Password=xxxx;Provider=OraOLEDB.Oracle;"
providerName="System.Data.OleDb"/>

.cs file

public DataTable req_rpt()
{

const string cursor_return_name = "1";

OleDbConnection myConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["myCtring"].ConnectionString);
myConnection.Open();

OleDbCommand cmd = new OleDbCommand("xxx.pkg_rpt.s_rpt", myConnection);

cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(cursor_return_name, OleDbType.RefCursor, ParameterDirection.ReturnValue);

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

myConnection.Dispose();
myConnection.Close();

return dt;

}

Answer 1

User269602965 posted

Use Oracle ODP.NET managed or unmanaged driver, Oracle Data Access (ODAC)  OracleDataAccess.dll

http://docs.oracle.com/database/121/ODPNT/toc.htm

// Get data from stored procedure 

{
  try {
    string strGroup = "DUCK";
    string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
    using (OracleConnection conn = new OracleConnection(connstr)) {
      using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.getBirdInfo.rcSelectBirdNames", conn)) {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Clear();
          cmd.Parameters.Add("bv_BirdGroup", OracleDbType.Varchar2, strGroup, ParameterDirection.Input);
          cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output);
        conn.Open();
          using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) {
            DataSet ds = new DataSet();
            oda.Fill(ds);
            this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
          }
        }
      }
    } catch (Exception ex) {
  }
}

Oracle PL/SQL code
    
CREATE OR REPLACE PACKAGE {SCHEMANAME}.getBirdInfo AS TYPE refCursor IS REF CURSOR;
  PROCEDURE rcSelectBirdNames(bv_BirdGroup IN Varchar2, ListBirdNames OUT refCursor);
END getBirdInfo;
/

CREATE OR REPLACE PACKAGE BODY getBirdInfo AS

PROCEDURE rcSelectBirdNames(bv_BirdGroup IN Varchar2, ListBirdNames OUT refCursor)
IS
BEGIN
  OPEN ListBirdNames FOR
    SELECT a.BIRD_GROUP, a.AOU_NUMBER, a.BIRD_NAME_KEY, b.BIRD_NAME  
    FROM {SCHEMANAME}.BIRDS a
    INNER JOIN LKUP_BIRD_NAME b ON a.BIRD_NAME_KEY = b.BIRD_NAME_KEY
    WHERE a.BIRD_GROUP = :bv_BirdGroup;
END;

END getBirdInfo;
/

Answer 2

User702049738 posted

Hi Lannie;

Thanks for the post. I know i can do it will ODAC but with ODAC I will have to include an OracleDataAccess.dll in my web application that is why I decided to go with OLEDB because I do not need any dll. However, the challenge is trying to return a ref cursor with it. this is why it has to be done with oledb.

thank you

Answer 3

User269602965 posted

http://docs.oracle.com/database/121/OLEDB/using.htm#OLEDB196

Search document for REF CURSOR for ADO examples.

Answer 4

User702049738 posted

Hi Lannie;

I tried using the examples given but i got the same error message. I am surprised oracle hasn`t updated the posting for visual studio 2015

Answer 5

User269602965 posted

If your environment is NOT FIPS-140 enabled, you can use the Oracle MANAGED ODAC driver.  The DLL is transportable with the application and all you need to change is the TNSNAMES.ORA to point to the correct database.

I have used the managed driver with ASP.NET, WPF, and Console applications without issues.

Answer 6

User702049738 posted

Hi

I think that will is the only option I have left. I will have to spend time searching and downloading the right dll..

Answer 7

User269602965 posted

http://docs.oracle.com/database/121/ODPNT/toc.htm

can read about managed driver here.

I use it to get refcursor data sets back.

Answer 8

User702049738 posted

Hi Lannie...I have read the documentation..I know how to get a ref cursor back for odbc..however, i want to learn how to get a ref cursor back for oledb. the documentation hasn't been too help this is why i posted my question here

Answer 9

User269602965 posted

The Managed Driver is not ODBC, it is Oracle Data Access for .NET ODP.NET.  It is the most efficient and easiest way to get a ref cursor back.

The Oracle Client is built into the Managed Driver DLL.  You just need to add the driver to your application /BIN folder with a copy of the tnsnames.ora file to point to the ORacle database, and you are nearly there with some specific settings in application or web config, and LOCAL reference to the DLL.

Answer 10

User702049738 posted

Hi Lannie..thanks for the correction. I am still confused on how this is related to OLEDB for Oracle. I know ODP.NET and ODBC are quite similar. They require either the installation of a driver and adding of the reference or addition of dll to bin and inclusion of the reference. Can you please explain how this is related to OLEDB.

Thank you

Answer 11

User269602965 posted

It is not.  ODBC and OLEDB are old technologies. 

I stopped using them years ago when .NET 2.0 came out.  I transitioned to Oracle Data Provider for .NET using the Oracle Data Access (ODAC).  At first they had an unmanaged driver which required installation of the Oracle SQL Client on the application server.  More recently they developed a managed driver which includes the Oracle Client in the same driver making it much more portable between development, test, and production.    The Oracle Data Access Managed driver you simply drop into the /BIN folder of your application.  ODAC directly supports the REFCURSOR object. 

As you are finding out with OLEDB, refcursor is not directly supported, requires a workaround with inconsistent documentation on how to do that, and not reliable ( that is; no examples seem to work well).

Answer 12

User702049738 posted

thanks for the explanation and help. I think my only option and best solution is to go and back and use ODAC.

Thank you

Answer 13

User702049738 posted

Hi Lannie;

I have found a working example that shows an OLEDB returning a stored procedure with the output as ref cursor..see link below and example

If your procedure has the following signature:

PROCEDURE someProcedure(identifier in varchar,out_cursor out CURSOR_DEFINITION);

Then your connectionstring should look like this:

Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true;Password=[password];User ID=[userid];Data Source=[DataSource]

<add name="ConnectionString" connectionString="Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true; Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = xxxx))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxx))); User Id = xxx; Password=xxx;" providerName="System.Data.OleDb.OleDbConnection"/>

Notice the following differences about this connectionstring

• PLSQLRSet=true   This states that resultsets can be returned from stored procedures.
• OLEDBNet=True   This means that OraOLEDB is compatible with the OLE DB .Net data provider. If you set this to true, it cannot be used with ADO

using( OleDbConnection oracleConn = new OleDbConnection( _connectionString ) )
{
   // open connection
   try{oracleConn.Open();}
   catch{throw new LogonException();}
   // build command
   OleDbCommand cmd = new OleDbCommand("{call someProcedure(?)}", oracleConn );
   cmd.CommandType = CommandType.Text;
   // add parameters
   cmd.Parameters.Add( "identifier", OleDbType.VarChar ).Value = identifier;
   OleDbDataAdapter da = new OleDbDataAdapter( cmd );
   // fill dataset
   DataSet ds = new DataSet();
   da.Fill( ds);
   // all done, return
   return ds;
}
 

public DataTable testme()
{
using (OleDbConnection myConnection = new OleDbConnection(meConnectionString()))
{
myConnection.Open();
OleDbCommand cmd = new OleDbCommand("{call finance.PKG_test.test_info}", myConnection);

cmd.CommandType = CommandType.Text;

OleDbDataReader ord = cmd.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(ord);

return dt;

}

}

https://weblogs.asp.net/mnolton/38877