[HACKERS] Oracle to PostGre

[Brahmam Eswar]

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.