Retrieving information from Oracle to VB.NET

I am using Oracle 10g and I have tried several things to write a procedure that I can access using VB.NET. I am using th Enterprise Library for June 2005 so i am bypassing using ADO.NET. I have created my data connection using the Createdatabase tool and I want to grab the needed data and drop it into a data collection which will then be used as the data source on a windows datagrid.

I have the datagrid coded and the data objects coded. I am including my VB code for this and I will include my Oracle Package as well. The package runs correctly when I run it in Jdeveloper in debug mode. I can not get REF CURsors to work correctly. I get an error on the VB side that says Wrong Number or Types of Arguments.

Any ideas are greatly appreciated.

PACKAGE "my_pkg" AS
TYPE user_cursor IS REF CURSOR RETURN DB.table1%rowtype;
TYPE tbl_users IS TABLE of DB.table1%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE GETUSER(aLname IN VARCHAR2, o_users OUT tbl_users);
END my_pkg;

PACKAGE BODY "my_pkg_body" AS
--This returns an array containing all users will the last name specified.

PROCEDURE GETUSER(aLname IN VARCHAR2, o_users OUT tbl_users)
IS
CURSOR u1 IS
SELECT * FROM DB.Table1 WHERE UPPER(lastname) = aLname;

percount NUMBER DEFAULT 1;

BEGIN
FOR u IN u1 LOOP
o_users(percount) := u;
percount := percount + 1;
END LOOP;
END GetUser;

END my_pkg_body;

VB Code - -------

Public Shared Function GetUser(ByVal lname As String) As UsersDOCollection

Dim db As Database = _oracleDatabase
Dim sProc As String = "DB.my_pkg.GetUser"
Dim cmd As DBCommandWrapper = db.GetStoredProcCommandWrapper(sProc)
Dim myname As String = "'" + UCase(lname) + "'"

cmd.AddInParameter("aLname",DbType.String, myname)
'Im not sure what to put here since Im returning a table type from oracle. I will put the oracle type of cursor here for returning a cursor type
cmd.AddOutParameter("user_cursor",oracleType.Cursor,10)

Dim dr As OracelDatareader = db.ExecuteReader(cmd)

Dim users As UsersDOCollection = New UsersDOCollection

While dr.read()
Dim u As UsersDO = New UsersDO
u.ID = dr("user_id").tostring()
u.firstname = dr("firstname").tostring()
u.lastname = dr("lastname").tostring()

user.Add(u)
End While
dr.close()
Return User


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories