From: | Brahmam Eswar <brahmam1234(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Oracle to PostGre |
Date: | 2017-11-01 11:55:34 |
Message-ID: | CA+wLFo3hZ1Ov=HtWHTgnoksWeofytPUkaD=3YGi4=hqt=AN4Lg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2017-11-01 12:21:21 | Re: Try to fix endless loop in ecpg with informix mode |
Previous Message | Pavel Stehule | 2017-11-01 11:44:31 | Re: Re: proposal - psql: possibility to specify sort for describe commands, when size is printed |