Howdy, Stranger!

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

Categories

Retrieving information from Oracle to VB.NET

ttrig324ttrig324 Member Posts: 2
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.