How To Run A Stored Procedure That Returns A Ref Cursor And Reat Returned Values

Private Sub PopulateRegistry()


Dim registryID As String
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD.World)));" _
+ "User Id=myid;Password=mypass;"

Dim myConnection As New OracleConnection(oradb)

myConnection.Open()

Dim sql As String = "ACPG_V1_PKG.ADD_REGISTRY"
Dim readMaxRegIDSql As String = "select max( "
Dim cmdAddRegistry As New OracleCommand(sql, myConnection)



'Find out what the next value of ACPG_Registry.

cmdAddRegistry.CommandType() = CommandType.StoredProcedure
cmdAddRegistry.Parameters.Add(New OracleParameter("p_registry_name", OracleDbType.Varchar2)).Value = "1"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_owner_ncid", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_owner_type", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_status", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_restricted_flag", OracleDbType.Char)).Value = "1"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_default_dx_medcin_id", OracleDbType.Int64)).Value = 1
cmdAddRegistry.Parameters.Add(New OracleParameter("p_URL", OracleDbType.Varchar2)).Value = "1"
cmdAddRegistry.Parameters.Add(New OracleParameter("p_created_date", OracleDbType.Date)).Value = Now
cmdAddRegistry.Parameters.Add(New OracleParameter("p_created_by_ncid", OracleDbType.Int64)).Value = 1
Dim prm = New OracleParameter("curVar", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
cmdAddRegistry.Parameters.Add(prm)


cmdAddRegistry.ExecuteNonQuery()

Dim oraref As OracleRefCursor = cmdAddRegistry.Parameters("curVar").Value

Dim dr As OracleDataReader = oraref.GetDataReader()

Dim RetVal As Integer

dr.Read()

RetVal = dr(0)


Assert.GreaterThan(RetVal, 0)

'Verify that the new row in the ACPG Registry exists

Dim mystringRegistryId As String = "SELECT MAX(Registry_ID) as maxID from acpg_registry R"

Dim cmdCheckRegistry As New OracleCommand(mystringRegistryId, myConnection)





Dim Ridds As New DataSet
Dim Ridda As OracleDataAdapter
Dim Riddt As DataTable
Ridda = New OracleDataAdapter(cmdCheckRegistry)
Ridda.Fill(Ridds, "R")
Riddt = Ridds.Tables("R")

Dim Rid As Integer = Riddt.Rows(0).Item(0)

Assert.AreEqual(Rid, RetVal)






















'








End Sub