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
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