Returning PL/SQL Tables

PL/SQL tables are mainly used for accessing array of PLSQL data. The OraParamArray object in OO4O can be used to hold a PL/SQL cursor variable.

The OraParamArray object representing a table variable should be created first using AddTable method. Table values are accessed or set using the Get_Value and Put_Value methods of OraParamArray object. The following PLSQL procedure returns an array of ENAME value for array of EMPNOs.

CREATE PACKAGE EmpNames AS

type NUMARRAY is table of NUMBER index by

BINARY_INTEGER; --Define EMPNOS array

type VCHAR2ARRAY is table of VARCHAR2(10) index by

BINARY_INTEGER; --Define ENAMES array

PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER,

inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY);

END EmpNames;

CREATE PACKAGE BODY EmpNames AS

PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER,

inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY) is

BEGIN

FOR I in 1..ArraySize loop

SELECT ENAME into outEmpNames(I) from EMP

WHERE EMPNO = inEmpNos(I);

END LOOP;

END;

END EmpNames;

The following example executes the previous procedure to get the ename table

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set Empdb = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0)

Empdb.Parameters.Add "ArraySize", 3, ORAPARM_INPUT

Empdb.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER,

3, 22

Empdb.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, ORATYPE_VARCHAR2,

3, 10

Set EmpnoArray = Empdb.Parameters("EMPNOS")

Set EnameArray = Empdb.Parameters("ENAMES")

'Initialize the newly created input parameter table EMPNOS

EmpnoArray(0) = 7698

EmpnoArray(1) = 7782

EmpnoArray(2) = 7654

Empdb.ExecuteSQL ("Begin EmpNames.GetEmpNamesInArray(:ArraySize,

:EMPNOS, :ENAMES); End;")

MsgBox EnameArray(0)

MsgBox EnameArray(1)

MsgBox EnameArray(2)