Oracle to PostGre

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.

Responses

Browse pgsql-hackers by date

  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