As a brief note, this is probably not the best list for this. You would do better to ask questions like this on -general where you have more application developers and so forth. This is more of an SQL question so asking people who are hacking the codebase may not be the best way to get it answered.
Also, it is Postgres or PostgreSQL. People will assume you are totally new if you call it Postgre. On Wed, Nov 1, 2017 at 12:55 PM, Brahmam Eswar <brahmam1...@gmail.com> wrote: > 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. > When you ask on -general, please include the query which is actually causing the problem. My guess is that either you didn't declare the type properly or there is some other error in your function, but the information provided is not sufficient to answer it. Best or luck asking on -general. -- Best Regards, Chris Travers Database Administrator Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com Saarbrücker Straße 37a, 10405 Berlin
