Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Oracle to PostgreSQL

[GENERAL] Oracle to PostgreSQL

From
Brahmam Eswar
Date:
Hi ,

How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.

--
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL

From
Laurenz Albe
Date:
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

Re: [GENERAL] Oracle to PostgreSQL

From
Brahmam Eswar
Date:
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.

Re: [GENERAL] Oracle to PostgreSQL

From
Laurenz Albe
Date:
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

Re: [GENERAL] Oracle to PostgreSQL

From
"Felix Kunde"
Date:
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