Re: [HACKERS] Oracle to PostGre - Mailing list pgsql-hackers

Hi,

App is moving to Postgre from Oracel . After migrating the store procedure is throwing an error with collection type.

Oracle :

create or replace PROCEDURE    "PROC1"

 (

   

 , REQ_CURR_CODE IN VARCHAR2

 , IS_VALID OUT VARCHAR2

 , ERROR_MSG OUT VARCHAR2

 ) AS

  

   

   TYPE INV_LINES_RT IS RECORD(

         VENDOR_NUM AP.CREATURE_TXN_LINE_ITEMS.VENDOR_NUM%TYPE,

         VENDOR_SITE_CODE AP.CREATURE_TXN_LINE_ITEMS.VENDOR_SITE_CODE%TYPE,

         INVOICE_NUM AP.CREATURE_TXN_LINE_ITEMS.INVOICE_NUM%TYPE,

         TXN_CNT NUMBER

   );

   TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;

   L_INV_LINES INV_LINES_T;

   IS_MULTI_VENDOR FINO_APRVL_BUS_CHANN_DEFAULTS.MULTI_VENDOR_REQ_ALLOWED%TYPE;

   BUS_CHANNEL_RECORD FINO_APRVL_BUS_CHANN_DEFAULTS%ROWTYPE;

    CAL_APRVL_AMT_BY_TOTAL_AMT FINO_APRVL_BUS_CHANN_DEFAULTS.CAL_APR_AMT_BY_INV%TYPE;

   

  

Postgre : 

create or replace FUNCTION AP.VALIDATE_CRTR_LINE_ITEMS

(

REQ_CURR_CODE IN VARCHAR,

IS_VALID OUT VARCHAR,

ERROR_MSG OUT VARCHAR

) AS $$

DECLARE

        

    INV_LINES_T ap.validate_crtr_line_items$inv_lines_rt ARRAY;

    L_INV_LINES INV_LINES_T%TYPE;

    L_INV_LINES$temporary_record ap.validate_crtr_line_items$inv_lines_rt;

    IS_MULTI_VENDOR AP.FINO_APRVL_BUS_CHANN_DEFAULTS.MULTI_VENDOR_REQ_ALLOWED%TYPE;

    BUS_CHANNEL_RECORD ap.fino_aprvl_bus_chann_defaults%ROWTYPE;

     CAL_APRVL_AMT_BY_TOTAL_AMT AP.FINO_APRVL_BUS_CHANN_DEFAULTS.CAL_APR_AMT_BY_INV%TYPE;

    

but it's throwing an error as : 0 SQLState: 42P01 Message: ERROR: relation "l_inv_lines" does not exist

--

Thanks & Regards,
Brahmeswara Rao J.