Re: Stored Procedure question

Try looking up "pipelined function" on asktom.oracle.com.

--Peter

sahcutie wrote:
> I am a newbie in oracle and I have written a stored procedure that
> should return multiple records. I have implemented cursors and I was
> wondering if anyone would point out what is wrong. I have pasted my
> stored procedure below:
> Any help is appreciated. Also any links to help me along the path will
> also be really appreciated.
>
> CREATE OR REPLACE PROCEDURE VINFO
> (
> "LASTNAME" IN VARCHAR2) IS
>
> TYPE varVendorNumber is TABLE OF VARCHAR2(10);
> TYPE varVendorAddress1 is TABLE OF VARCHAR2(35);
> TYPE varVendorAddress2 is TABLE OF VARCHAR2(35);
> TYPE varVendorCity is TABLE OF VARCHAR2(30);
> TYPE varVendorState is TABLE OF VARCHAR2(6);
> TYPE varVendorPostal is TABLE OF VARCHAR2(12);
> TYPE varVendorCountry is TABLE OF VARCHAR2(3);
> VenNum varVendorNumber;
> VenAdd1 varVendorAddress1;
> VenAdd2 varVendorAddress2;
> VenCity varVendorCity;
> VenState varVendorState;
> VenPostal varVendorPostal;
> VenCountry varVendorCountry;
>
> CURSOR C_Vendor IS select a.VENDOR_ID, c.ADDRESS1, c.ADDRESS2,
> c.CITY, c.STATE, c.POSTAL, c.COUNTRY
> from PS_VENDOR a, PS_VENDOR_PAY b, PS_VENDOR_ADDR c
> where a.NAME1 like '%' || LASTNAME || '%'
> and a.VENDOR_ID = b.vendor_id
> and a.vendor_id = c.vendor_id
> and b.PYMNT_GROUP_CD = 'EM'
> and a.VENDOR_STATUS = 'A'
> and c.eff_status = 'A'
> and c.ADDRESS_SEQ_NUM = a.PRIM_ADDR_SEQ_NUM;
>
> BEGIN -- executable part starts here
>
> OPEN C_Vendor;
> FETCH C_Vendor BULK COLLECT INTO VenNum, VenAdd1, VenAdd2,VenCity,
> VenState, VenPostal,VenCountry;
> CLOSE C_Vendor;
>
> END "VINFO";
>
Received on Thu Oct 13 2005 - 00:31:56 CEST