This is my take on how this works on the server side and why SQLExec() is not capable of doing what you want.

My primary reference is Oracle PL/SQL Programming by Scott Urman [Oracle Press] as well as numerous Oracle training and reference manuals. Everything I see [including your code], still operates 1 row at a time. While you are building an array of values, once the procedure ends, the resources [the array, cursor, variables, etc.] are released or closed once the procedure ends. So there is nothing remaining to retrieve.

I think this is really a limitation of SQLExec(). When the server-side SP is called by SQLExec(), the SP runs to completion. Because the procedure has ended and the resources have been released, there is nothing remaining for a second SQLExec() to retrieve.

I believe the capability you are looking for is something like:

SQLExec(nHandle, 'EXEC MyProc', @aResults)

... where the SP can return a results array to aResults. This is definitely not supported in SQLExec(), and, AFAIK, is not supported in the Oracle SP.

I would guess ADO works because you have client-side capabilities that are just not available in the crude [but useful] SQLExec() function.

>This is a simple proof of concept snipit:
>
>CREATE OR REPLACE PACKAGE VendorSites AS
>
> TYPE t_VendorNames IS
> TABLE OF vendors.vendor_name%TYPE
> INDEX BY BINARY_INTEGER;
>
> TYPE t_SiteNames IS
> TABLE OF sites.site_name%TYPE
> INDEX BY BINARY_INTEGER;
>
> TYPE t_SiteCodes IS
> TABLE OF sites.site_code%TYPE
> INDEX BY BINARY_INTEGER;
>
> PROCEDURE GET_SITES_FOR_VENDOR
> ( tnVendorCode IN vendors.vendor_code%TYPE,
> VendorNames OUT t_VendorNames,
> SiteNames OUT t_SiteNames,
> SiteCodes OUT t_SiteCodes );
>
> PRAGMA RESTRICT_REFERENCES(GET_SITES_FOR_VENDOR, WNDS);
>END VendorSites ;
>/
>
>CREATE OR REPLACE PACKAGE BODY VendorSites AS
> /******************************************************************************
> NAME: GET_SITES_FOR_VENDOR
> PURPOSE: To find all sites for a given vendor
>
> REVISIONS:
> Ver Date Author Description
> --------- ---------- --------------- ------------------------------------
> 1.0 1/28/00 Bill Elvin 1. Created this procedure.
>
> PARAMETERS:
> INPUT: tnVendorCode
> EXAMPLE USE: GET_SITES_FOR_VENDOR(4);
>******************************************************************************/
> PROCEDURE GET_SITES_FOR_VENDOR
> ( tnVendorCode IN vendors.vendor_code%TYPE,
> VendorNames OUT t_VendorNames,
> SiteNames OUT t_SiteNames,
> SiteCodes OUT t_SiteCodes )
> AS
> CURSOR c_VendorSites IS
> SELECT
> vendor_name,
> site_code,
> site_name
> FROM
> vendors,
> sites
> WHERE
> vendors.vendor_code = sites.vendor_code
> AND vendors.vendor_code = tnVendorCode ;
>
> i NUMBER := 0 ;
>
> BEGIN
> FOR v_VendorSites IN c_VendorSites LOOP
> i := i + 1 ;
> VendorNames(i) := v_VendorSites.vendor_name ;
> SiteCodes(i) := v_VendorSites.site_code ;
> SiteNames(i) := v_VendorSites.site_name ;
> END LOOP ;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> Null;
> WHEN OTHERS THEN
> Null;
> END GET_SITES_FOR_VENDOR ;
>END VendorSites ;
>/