Calling an Oracle Package using DBProviderFactory

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

Answer this question

Calling an Oracle Package using DBProviderFactory

GeoffMF

So that we can close out the question, what was the problem
Jargo

Is cursors.data_cursor defined as an Oracle REF_CURSOR
cookie24
Here is my package code:

Procedure Get_Programs
(
cursor1 out cursors.data_cursor
)
is
begin
OPEN cursor1 FOR SELECT
SHELL_GROUP,
SHELL_#,
LOCAL_EXE_LOCATION,
NETWORK_EXE_LOCATION,
PROGRAM_NAME,
APPLICATION_NAME,
SHELL_PROGRAM_GROUPS_#
FROM
WZUSER.SHELL_PROGRAMS
inner join
wzuser.shell_program_groups on
wzuser.shell_programs.shell_program_groups_# = wzuser.shell_program_groups.shell_program_group_#;
end get_programs;
end;
tskinner

I gave your code a cursory glance but I didn't see a problem. Perhaps it's how you have your package and sp defined Below is an example:

How to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider
Abhinesh Jadhav
Here is the definition from my DB

PACKAGE "CURSORS" AS TYPE Data_cursor IS REF CURSOR;
END CURSORS;
Andy Ng
Nevermind, I found the problem. Thanks for the help.

John
vikassony
The problem was setting the variable's dbtype. Once the parameter was set to an Oracle type, I cast a local variable of type oracle parameter and set the oracletype = cursor and it worked. So now, when the parameter comes in I check it's type, if it is Oracle I recast a local variable to an oracle parameter and set it's oracletype, otherwise I pass it along. It would be nice to resolve this issue in the next version of ADO or with a service pack. Oracle's driver does solve this and participates in the factory model so I know it is possible, and from the looks of it, there are others who would like to see a cursor or refcursor type in the common namespace.

John
Calling an Oracle Package using DBProviderFactory

Answer this question