"John Wright" <riley_wri***@notmail.com> wrote in message news:Oh3jNAhlGHA.4100@TK2MSFTNGP05.phx.gbl...
>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
>
>