Dotconnect Enterprise Library --> parameter of type "Cursor"

[goethals.f]

Hello,

I have a question about the dotconnect/devart Enterprise Library concerning accessing/executing a stored procedure where one of the parmeter is an out-parameter of sys_refcursor --> see oracle procedure header below

Code: Select all

PROCEDURE SELECT_SECTION_BY_ID (p_Section_ID IN NUMBER, p_ResultSet OUT sys_refcursor)

In my first app I used the direct dotconnect objects (connection, parameter, OracleCursor, OracleReader) which works fine.

But how do you do the same with the Enterprise Library

I now have

Code: Select all

Dim tmpDatabase As Devart.Data.Oracle.EnterpriseLibrary.OracleDatabase = Nothing
Dim tmpConnection As Data.Oracle.OracleConnection = Nothing

If tmpDatabase Is Nothing Then
 tmpDatabase = DatabaseFactory.CreateDatabase()
 tmpConnection = tmpDatabase.CreateConnection()
End If


Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)

[b]tmpDatabase.AddOutParameter(_oracleCommand, "p_ResultSet", OracleDbType.Cursor, Nothing)[/b]

How do you have to specify the last parameter, so that it is of the specific cursor-type ?

and how te retrieve the cursor afterwards (via ExecuteNonQuery -> parameters -> GetDataReader)

thx
Fred

[goethals.f]

when I leave the out-parameter out, (so just 1 input-param) and use the method "ExecuteDataSet" then I got an dataset back wich has the results in it.

But,

*) is there a simple way by using in-out params, and just a simple dataReader to get the ref-cursor back
*) can you specify in an oracle proc 2 out-parameters as cursor --> and how do you do that via dotconnect (if ExecuteDataSet only returns 1 datatset)


thx
Fred

[Shalex]

Please don't add out parameters to the collection - out parameters will be initialized after stored procedure execution:

• you can get the result set as data reader in the following way:

  Code: Select all

          Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
          tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)
  
          tmpDatabase.ExecuteNonQuery(_oracleCommand)
          Dim reader As OracleDataReader = DirectCast(tmpDatabase.GetParameterValue(_oracleCommand, "p_ResultSet"), OracleCursor).GetDataReader

• if your stored procedure contains 2 out cursor parameters, and you execute the ExecuteDataSet method, the result DataSet will contain two tables (Table and Table1) corresponding to your two result sets.

[goethals.f]

Please don't add out parameters to the collection - out parameters will be initialized after stored procedure execution:

• you can get the result set as data reader in the following way:

  Code: Select all

          Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
          tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)
  
          tmpDatabase.ExecuteNonQuery(_oracleCommand)
          Dim reader As OracleDataReader = DirectCast(tmpDatabase.GetParameterValue(_oracleCommand, "p_ResultSet"), OracleCursor).GetDataReader

• if your stored procedure contains 2 out cursor parameters, and you execute the ExecuteDataSet method, the result DataSet will contain two tables (Table and Table1) corresponding to your two result sets.

OK

thx for the info. But now I have a small extra questions.
When I was doing some more test and had the DBMonitor running in the background.

I saw that by each execution of 'GetStoredProcCommand' there was a new connection made, openend, fetched the results and closed (close statement info was logged in the list). Even when I had my OracleDatabase and OracleConnection as global variables.
In the left tree I sas multiple connection-objects (which have each unique nr's and each have one executed command)

Is this the normal behaviour that after each execution the connection to the oracle DB is closed. Is there a setting to keep the connection really open and close it manually when I want. Or is the connection not really closed (but why it is then eacht time listed in the DBMonitor)

thx again

Fred

[Shalex]

We provide only interface of using our provider to Enterprise Library. Connection is used according to an internal implementation of Enterprise Library. But you can use the connection pooling feature of our provider (Pooling=true; in the connection string), which is turned on by default. In the case when Pooling=true; there will be only one connection from the database side (you can check the opened sessions with some tool) even though DBMonitor will display several connections.