DBFactory and Oracle Cursor

  • Thread starter Thread starter John Wright
  • Start date Start date
J

John Wright

I am trying to create a generic DAL and I am almost there. I can query the
Oracle Database without any problem using straight sql, but when I try to
call returning a cursor I get the following error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_PROGRAMS'

I have attached the code that executes to return the datatable. As you can
see I check the type of the parameter and if it is an object and direction
output, I am assuming an oracle cursor type and translate it. But when I do
an autos watch on my debugging I can see the parameter is an oracleparameter
and the direction is output, but it creates a date type not a cursor type as
my code suggests.

All I want is a way to call an Oracle package and return a result set. If
this is not the way, can someone point me in the right direction or help fix
this code? Thanks.

John


'DAL factory code
'client code


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click

Dim x As New DAL.DALManager()

Dim y As New DAL.DbParameterCollection

x.ProviderName = "System.Data.OracleClient"

x.ConnectionString = "User Id=[DBID];Password=[DBPassword];Data
Source=[DBSource]"

'Dim dt As DataTable = x.ExecuteDataTableSQL("SELECT * FROM
WZUSER.SHELL_PROGRAMS")

y.CreateParameter("Programs", Nothing, DbType.Object,
ParameterDirection.Output)

Dim dt As DataTable = x.ExecuteDataTable("WZUSER.WZSHELL.Get_Programs", y)

DataGridView2.DataSource = dt

End Sub





'DAL Code

Public Function ExecuteDataTable(ByVal CommandText As String, ByVal
ParameterCollection As DbParameterCollection) As DataTable

Dim dt As New DataTable

Using conn As DbConnection = GetDBConnection()

conn.Open()

Dim dbCmd As DbCommand = conn.CreateCommand

With dbCmd

.CommandText = CommandText

.CommandType = CommandType.StoredProcedure

.Connection = conn

End With

For Each oParam As DbParameter In ParameterCollection

Dim oDBParam As System.Data.Common.DbParameter =
dbCmd.CreateParameter()

With oDBParam

.ParameterName = oParam.Name

.Value = oParam.Val

If TypeOf oDBParam Is
System.Data.OracleClient.OracleParameter AndAlso oParam.Direction =
ParameterDirection.Output Then

'Check the type. If it is an oracle type and
object and output, set to cursor

.DbType =
System.Data.OracleClient.OracleType.Cursor

Else

.DbType = oParam.DBType

End If

.Direction = oParam.Direction

End With

dbCmd.Parameters.Add(oDBParam)

Next

Dim dr As DbDataReader = dbCmd.ExecuteReader

dt.Load(dr)

dr.Close()

End Using

Return dt

End Function
 
Back
Top