1. Returns an array (Author: enhydraboy (Flurry of dust))
Create a package or stored procedure Connect Scott / Tiger in the Oracle Background;
CREATE OR REPLACE PACKAGE ado_callpkg ASTYPE eid IS TABLE of NUMBER (4) INDEX BY BINARY_INTEGER; TYPE ename IS TABLE of VARCHAR2 (40) INDEX BY BINARY_INTEGER; PROCEDURE getEmpNames (empid OUT eid, empname OUT ename); end ado_callpkg;
CREATE OR REPLACE PACKAGE BODY ado_callpkg ASPROCEDURE getEmpNames (empid OUT eid, empname OUT ename) ISCURSOR c1 IS select employee_id, first_name || ',' || Middle_Initial || ',' || last_name as name from employee; cnt NUMBER DEFAULT 1; C C1% RowType; beginopen C1; LOOP FETCH C1 INTO C; EmpName (CNT): = C.Name; Empid (CNT): = C.employee_ID; Exit When C1% NotFound; - Process The Data CNT: = CNT 1; End loop; close c1; end; end ado_callpkg;
2 front desk VB program call
Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim cmd As New ADODB.Command Dim str As String str = "{call ado_callpkg.getEmpNames ({resultset 100, empid, empname})}" cn.Open "Provider = MSDAORA.1; Password = tiger; User ID = scott; Data Source = ORACLE; Persist Security Info = True "With cmd .CommandText = str .ActiveConnection = cn .CommandType = adCmdText End With rs.CursorLocation = adUseClient rs.Open cmd Do While Not Rs.eof Debug.Print Rs.fields (0) .Value & Vbtab & Rs.fields (1) .value rs.Movenext Loop
----------------
