Execution Environment:
Visual Basic 6.0
Access Privileges:
Requires the ability to create and execute stored procedures.
Usage:
Run the console application from a command prompt or from within Visual Basic.
Instructions:
1. Set up the following PL/SQL package in SQL*Plus:
CREATE OR REPLACE PACKAGE retcursor AS
TYPE tempno is TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE tename is TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE tjob is TABLE of VARCHAR2(9) INDEX BY BINARY_INTEGER;
PROCEDURE allemp (empno OUT tempno, ename OUT tename, job OUT tjob);
PROCEDURE oneemp (oneempno IN NUMBER, empno OUT tempno,ename OUT tename, job OUT tjob);
END retcursor;
/
2. Set up the following package body in SQL*Plus:
CREATE OR REPLACE PACKAGE BODY retcursor AS
PROCEDURE allemp(empno OUT tempno, ename OUT tename, job OUT tjob) IS
CURSOR emp_cur IS SELECT empno, ename, job FROM emp;
empcount NUMBER DEFAULT 1;
BEGIN
FOR singleemp IN emp_cur LOOP
empno(empcount) := singleemp.empno;
ename(empcount) := singleemp.ename;
job(empcount) := singleemp.job;
empcount := empcount + 1;
END LOOP;
END;
PROCEDURE oneemp(oneempno IN NUMBER, empno OUT tempno, ename OUT tename, job OUT tjob) IS
CURSOR emp_cur IS SELECT empno, ename, job FROM emp WHERE empno = oneempno;
empcount NUMBER DEFAULT 1;
BEGIN
FOR singleemp IN emp_cur LOOP
empno(empcount) := singleemp.empno;
ename(empcount) := singleemp.ename;
job(empcount) := singleemp.job;
empcount := empcount + 1;
END LOOP;
END;
END;
/
3. Create three named buttons on your Visual Basic Form:
cmdAll
cmdOne
cmdByeBye
All returned data will be displayed with Message Boxes
4. This sample requires the use of the Microsoft ODBC driver for Oracle, version 2.573.2927.00
or above, as detailed in the Description section of this article.
Description
Description:
This sample will not work with the Oracle ODBC Driver, since the syntax used
is specific to Microsoft's ODBC driver for Oracle. See <BUG:866921> for
further information.
Prerequisites:
* Microsoft ODBC Driver for Oracle, version 2.573.2927.00 or above
* Oracle RDBMS version 8.0.5 or above
* Oracle client software version 8.0.5 or above
* Visual Basic 6.0
Sample Output:
A message box is output that contains the emp ID, ename, job.
References
<BUG:866921> ODBC Fails to Return Result From a Stored Procedure That Has Cursors
Information in this article was taken from Oracle Sample Code Repository Entry #782.
Sample Code
Option Explicit
' Global variables to set up connection
Dim Conn As ADODB.Connection
Dim AllCmd As ADODB.Command
Dim OneCmd As ADODB.Command
Dim RecSet As ADODB.Recordset
Private Sub Command1_Click()
End Sub
Private Sub Form_Load()
Dim ConnStr As String
Dim SQLStr As String
ConnStr = "UID=scott;PWD=tiger;DSN=dsn817;"
' Make connection
Set Conn = New ADODB.Connection
With Conn
.ConnectionString = ConnStr
.CursorLocation = adUseClient
.Open
End With
' Get cursor from Stored proc containing all employees
' the number in resultset xx must be higher or equal to rows
' in cursor
SQLStr = "{call retcursor.allemp({resultset 20, " _
& "empno, ename, job})}"
Set AllCmd = New ADODB.Command
With AllCmd
Set .ActiveConnection = Conn
.CommandText = SQLStr
.CommandType = adCmdText
End With
' Sets up to get one emp from cursor with specific empno
SQLStr = "{call retcursor.oneemp(?,{resultset 2, " _
& " empno, ename, job})}"
Set OneCmd = New ADODB.Command
With OneCmd
Set .ActiveConnection = Conn
.CommandText = SQLStr
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With
' Set up recordset
Set RecSet = New ADODB.Recordset
With RecSet
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub cmdAll_Click()
' Display each row of record set in Message box
Set RecSet.Source = AllCmd
RecSet.Open
While Not RecSet.EOF
MsgBox "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
RecSet.MoveNext
Wend
RecSet.Close
End Sub
Private Sub cmdOne_Click()
' Display specific employee in message box
Dim inputssn As Long
Set RecSet.Source = OneCmd
inputssn = InputBox("Enter the Employee # you wish to retrieve:")
OneCmd(0) = inputssn
RecSet.Open
MsgBox "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
RecSet.Close
End Sub
Private Sub Form_Unload(Cancel As Integer)
' Clean up
Conn.Close
Set Conn = Nothing
Set AllCmd = Nothing
Set OneCmd = Nothing
Set RecSet = Nothing
End Sub
Private Sub cmdByeBye_Click()
End
End Sub
