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 ;
/
Thanx,
Bill Elvin