Обсуждение: [GENERAL] Oracle to PostgreSQL
Hi ,
--
How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.
Thanks & Regards,
Brahmeswara Rao J.
Brahmeswara Rao J.
Brahmam Eswar wrote: > How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres. Are you talking about table definitions or PL/SQL code? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Here is the snippet of it.
Oracle :
Declaration part in Store Procedure
CREATE OR REPLACE PROCEDURE "A"."Datastore"
(
, In_Param1 IN VARCHAR2
, In_Param2 IN VARCHAR2
, In_Param3 IN VARCHAR2
, Out_Param1 OUT VARCHAR2
, ERROR_MSG OUT VARCHAR2
) AS
TEMP_ERR_MSG VARCHAR2(4000);
IS_FIRST CHAR(1);
TYPE INV_LINES_RT IS RECORD(
VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
TXN_CNT NUMBER
);
TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;
L_INV_LINES INV_LINES_T;
Looping Part :
IS_FIRST:='Y';
WITH Distinct_Invoices As ( select DISTINCT VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM,DESCRIPTION,CURR_CODE,INVOICE_AMOUNT,IMAGE_NUMBER, VENDOR_NAME, PAYMENT_METHOD, GL_DATE, TERMS_NAME, RETURN_ID, PAYGROUP, INVOICE_TYPE, INVOICE_DATE from A.Datastore where CASE_ID = In_Param1 )
Select VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM, count(*) as TXN_CNT BULK COLLECT INTO L_INV_LINES from Distinct_Invoices group by VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM having count(*) > 1;
IF L_INV_LINES.COUNT>0 THEN
ERROR_COUNT:=0;
FOR indx in L_INV_LINES.first .. L_INV_LINES.last LOOP
IF IS_FIRST!='Y' THEN
TEMP_ERR_MSG := TEMP_ERR_MSG || ', ';
END IF;
IS_FIRST:='N';
TEMP_ERR_MSG := TEMP_ERR_MSG || '''' || L_INV_LINES(indx).INVOICE_NUM || '''';
How do i convert this into PostgreSQL.
On Thu, Nov 9, 2017 at 4:48 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
Brahmam Eswar wrote:
> How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.
Are you talking about table definitions or PL/SQL code?
Yours,
Laurenz Albe
Thanks & Regards,
Brahmeswara Rao J.
Brahmeswara Rao J.
On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote: > Here is the snippet of it. > TYPE INV_LINES_RT IS RECORD( > VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE, > VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE, > INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE, > TXN_CNT NUMBER > ); You can create a composite type in PostgreSQL: CREATE TYPE complex AS (r integer, i integer); > TYPE INV_LINES_T IS TABLE OF INV_LINES_RT; You would use an array in this case: DECLARE carr complex[]; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote: > You can create a composite type in PostgreSQL: > CREATE TYPE complex AS (r integer, i integer); > You would use an array in this case: > DECLARE > carr complex[]; I've once faced the same thing, and did as Laurenz suggested. You will like Postgres' array support. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general