Migrate Oracle to PostgreSQL

Conversion Samples of Oracle to PostgreSQL

Ispirer Toolkit analyzes all object dependencies during the conversion process and provides not only line-by-line conversion, but resolves type conversions as well. The software understands and transforms the necessary inheritance dependencies. It parses the entire source code, builds an internal tree with all the information about the objects, and uses it in the migration process.

The code samples below provide you with more details.

• Oracle Collections conversion:

  Oracle

  1. CREATE TYPE employee AS OBJECT (
  2.     id NUMBER,
  3.     Name VARCHAR(300)
  4.     );
  5.   CREATE TYPE employees_tab IS TABLE OF employee;
  6.        
  7.   CREATE OR REPLACE PROCEDURE hire(EMPLOYEES in out employees_tab, id NUMBER, Name VARCHAR) AS
  8.   NEW_EMPLOYEES employees_tab := employees_tab();
  9.   BEGIN
  10.     EMPLOYEES.Extend(1);
  11.     EMPLOYEES(EMPLOYEES.count) := employee(id, Name);
  12.     FOR i IN EMPLOYEES.first..EMPLOYEES.last
  13.     LOOP
  14.       INSERT INTO emp_tab values (EMPLOYEES(i).id, EMPLOYEES(i).Name);
  15.     END LOOP;
  16.     INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES);
  17.     NEW_EMPLOYEES.Extend(EMPLOYEES.count);
  18.     NEW_EMPLOYEES.Delete;
  19.   END;

  → PostgreSQL

  1. CREATE TYPE employee AS(id NUMERIC,Name VARCHAR(300));
  2.   -- CREATE TYPE employees_tab IS TABLE OF employee;
  3.  
  4.   CREATE OR REPLACE PROCEDURE hire(INOUT EMPLOYEES employee[] , id NUMERIC, Name VARCHAR)
  5.   LANGUAGE plpgsql
  6.     AS $$
  7.     DECLARE
  8.     NEW_EMPLOYEES  employee[] default array[]:: employee[];
  9.   BEGIN
  10.     EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)+1] := null;
  11.     EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)] := row(id,Name);
  12.     FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1)
  13.     LOOP
  14.       INSERT INTO emp_tab  values(EMPLOYEES[i].id, EMPLOYEES[i].Name);
  15.     END LOOP;
  16.     INSERT INTO EMP_TAB  SELECT * FROM UNNEST(EMPLOYEES);
  17.     for i in 1 .. coalesce(array_length(EMPLOYEES,1),0) loop
  18.       NEW_EMPLOYEES[coalesce(array_length(NEW_EMPLOYEES,1),0)+1] := null;
  19.     end loop;
  20.     NEW_EMPLOYEES := array[]:: employee[];
  21.   END; $$;

• Package objects transformation:

  Oracle

  1. CREATE OR REPLACE EDITIONABLE PACKAGE BODY "COMMON_PKG" AS
  2.        
  3.   CUSTOMER_ID NUMBER(10,0);
  4.   TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  5.   ADDRES_LST VARCHAR2_AARAY;
  6.  
  7.   PROCEDURE PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR2, CUSTTYPE_ID VARCHAR2)
  8.   IS
  9.   CUR_ADDR VARCHAR2(500);
  10.   CUSTM_ID NUMBER;
  11.     v_int number;
  12.     V_RES  DBMS_UTILITY.lname_array;
  13.   l_clob clob;
  14.   cursor cus_addr(cur_id number) is select address from customer where cust_id =  cur_id or cust_id <5;  
  15.   BEGIN
  16.   l_clob := '';
  17.     select CUST_ID into CUSTM_ID
  18.       from customer
  19.       where CITY = CITY_NAME and CUST_TYPE_CD = CUSTTYPE_ID and ROWNUM <= 1;
  20.   open cus_addr(CUSTM_ID);
  21.   fetch  cus_addr into CUR_ADDR;
  22.   WHILE(cus_addr%found)
  23.   loop
  24.   l_clob := l_clob || CUR_ADDR||',';
  25.   fetch  cus_addr into CUR_ADDR;
  26.   end loop;
  27.   if cus_addr%isopen then
  28.   close cus_addr;
  29.   end if;
  30.   dbms_output.put_line( rtrim(l_clob,',') );
  31.  
  32.   END;  
  33.  
  34.  
  35.   PROCEDURE GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE DATE) is
  36.  
  37.   EMPLOYEE_REC EMPLOYEE%ROWTYPE;
  38.   cursor cur_start_date is select * from EMPLOYEE;  
  39.   BEGIN
  40.     open cur_start_date;
  41.   fetch cur_start_date into EMPLOYEE_REC;
  42.   WHILE(cur_start_date%found)
  43.   loop
  44.   if EMPLOYEE_REC.START_DATE < STRT_DATE THEN
  45.   goto loop_again;
  46.   end if;
  47.   DBMS_OUTPUT.PUT_LINE(EMPLOYEE_REC.FIRST_NAME || '  ' ||EMPLOYEE_REC.LAST_NAME );
  48.   <<loop_again>>
  49.   fetch cur_start_date into EMPLOYEE_REC;
  50.   end loop;
  51.   if cur_start_date%isopen then
  52.   close cur_start_date;
  53.   end if;
  54.   END;  
  55.  
  56.   END COMMON_PKG;

  → PostgreSQL

  1. CREATE SCHEMA  IF NOT EXISTS COMMON_PKG;        
  2.        
  3.   -- TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  4.  
  5.   DROP TYPE  IF EXISTS COMMON_PKG.GL_VAR_TYPE CASCADE;
  6.   CREATE type COMMON_PKG.GL_VAR_TYPE
  7.   as(CUSTOMER_ID BIGINT,
  8.   ADDRES_LST VARCHAR(50)[]);
  9.   CREATE OR REPLACE FUNCTION COMMON_PKG.INIT_GL_VAR()
  10.   RETURNS VOID LANGUAGE plpgsql
  11.     AS $$
  12.   BEGIN
  13.      
  14.     CREATE TEMPORARY TABLE  COMMON_PKG_GL_VAR AS SELECT(array[]:: VARCHAR(50)[]):: COMMON_PKG.GL_VAR_TYPE AS SWV_GL_VAR_VAL;
  15.     RETURN;
  16.     EXCEPTION
  17.     WHEN SQLSTATE '42P07' THEN
  18.       NULL;
  19.   END; $$;
  20.   CREATE OR REPLACE FUNCTION COMMON_PKG.GET_GL_VAR()
  21.   RETURNS COMMON_PKG.GL_VAR_TYPE LANGUAGE plpgsql
  22.     AS $$
  23.     DECLARE
  24.     SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE;
  25.   BEGIN
  26.      
  27.     RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
  28.     EXCEPTION
  29.     WHEN OTHERS THEN
  30.       PERFORM COMMON_PKG.INIT_GL_VAR();
  31.       RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
  32.   END; $$;
  33.   CREATE OR REPLACE PROCEDURE COMMON_PKG.SET_GL_VAR(SWP_GLVAR COMMON_PKG.GL_VAR_TYPE)
  34.   LANGUAGE plpgsql
  35.     AS $$
  36.   BEGIN
  37.      
  38.     UPDATE COMMON_PKG_GL_VAR SET SWV_GL_VAR_VAL = SWP_GLVAR;
  39.   END; $$;
  40.   CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_CUSTID_BY_PARAMS(CITY_NAME        VARCHAR,
  41.       CUST_ID_CD        VARCHAR,
  42.       OUT CUST_ID NUMERIC)
  43.   LANGUAGE plpgsql
  44.     AS $$
  45.     DECLARE
  46.     SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
  47.     IN_ID  BIGINT;
  48.   BEGIN
  49.    
  50.     select CUST_ID into STRICT IN_ID
  51.     from CUSTOMER
  52.     where CITY = CITY_NAME and CUST_TYPE_CD = CUST_ID_CD LIMIT 1;
  53.     CUST_ID := IN_ID;
  54.   END; $$;
  55.   CREATE OR REPLACE PROCEDURE COMMON_PKG.PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR, CUSTTYPE_ID VARCHAR)
  56.   LANGUAGE plpgsql
  57.     AS $$
  58.     DECLARE
  59.     SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
  60.     CUR_ADDR  VARCHAR(500);
  61.     CUSTM_ID  NUMERIC;
  62.     v_int  NUMERIC;
  63.     V_RES  VARCHAR(4000)[] default array[]:: VARCHAR(4000)[];
  64.     l_clob  TEXT;
  65.     CUS_ADDR cursor(cur_id NUMERIC) FOR select ADDRESS from CUSTOMER where cust_id =  cur_id or cust_id < 5;
  66.   BEGIN
  67.    
  68.     l_clob := '';
  69.     select CUST_ID into STRICT CUSTM_ID
  70.     from CUSTOMER
  71.     where CITY = CITY_NAME and CUST_TYPE_CD = CUSTTYPE_ID LIMIT 1;
  72.     open CUS_ADDR(CUSTM_ID);
  73.     fetch  CUS_ADDR into CUR_ADDR;
  74.     WHILE FOUND
  75.     loop
  76.       l_clob := CONCAT(l_clob,CUR_ADDR,',');
  77.       fetch  CUS_ADDR into CUR_ADDR;
  78.     end loop;
  79.     if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUS_ADDR') then
  80.       close CUS_ADDR;
  81.     end if;
  82.     RAISE NOTICE '%',NULLIF(rtrim(l_clob,','),'');
  83.   END; $$;
  84.   CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE TIMESTAMP)
  85.   LANGUAGE plpgsql
  86.     AS $$
  87.     DECLARE
  88.     SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
  89.     EMPLOYEE_REC  EMPLOYEE%ROWTYPE;
  90.     CUR_START_DATE cursor FOR select * from EMPLOYEE;
  91.   BEGIN
  92.    
  93.     open CUR_START_DATE;
  94.     fetch CUR_START_DATE into EMPLOYEE_REC;
  95.     WHILE FOUND
  96.     loop
  97.       << loop_again >>
  98.       BEGIN
  99.         if EMPLOYEE_REC.START_DATE < STRT_DATE THEN
  100.           EXIT loop_again;
  101.         end if;
  102.         RAISE NOTICE '%',CONCAT(EMPLOYEE_REC.FIRST_NAME,'  ',EMPLOYEE_REC.LAST_NAME);
  103.       END;
  104.       fetch CUR_START_DATE into EMPLOYEE_REC;
  105.     end loop;
  106.     if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUR_START_DATE') then
  107.         close CUR_START_DATE;
  108.     end if;
  109.   END; $$;

• Hierarchical query conversion:

  Oracle

  1. create or replace Procedure sp_hier_with_cte_and_rownum (configList IN varchar2)
  2.   IS
  3.   refcur sys_refcursor;
  4.   BEGIN
  5.     FOR refcur IN (
  6.       with test as  
  7.       (select configList from dual)
  8.       select regexp_substr(configList, '[^;]+', 1, rownum) config
  9.       from test
  10.       connect by level <= length (regexp_replace(configList, '[^;]+'))  +1
  11.     )
  12.     LOOP
  13.       dbms_output.put_line('config= '||refcur.config);
  14.     END LOOP;
  15.   END;

  → PostgreSQL

  1. create or replace Procedure sp_hier_with_cte_and_rownum(IN configList VARCHAR)
  2.   LANGUAGE plpgsql
  3.   AS $$
  4.   DECLARE
  5.     refcur  REFCURSOR;
  6.     SWV_REFCUR_rec RECORD;
  7.   BEGIN
  8.     FOR SWV_REFCUR_rec IN(
  9.     WITH RECURSIVE
  10.     test as(select configList),
  11.     TabAl_cte AS(SELECT 1 AS LEVEL
  12.     UNION ALL
  13.     SELECT TabAl_cte.LEVEL+1 AS LEVEL        
  14.     FROM TabAl_cte, test
  15.     WHERE(TabAl_cte.LEVEL+1) <= length(regexp_replace(test.configList,'[^;]+','','g'))+1)
  16.     SELECT  SWF_REGEXP_SUBSTR(test.configList,'[^;]+',1,row_number() over()) AS config
  17.     FROM TabAl_cte,test)
  18.     LOOP
  19.       RAISE NOTICE '%',CONCAT('config= ',SWV_REFCUR_rec.config);
  20.     END LOOP;
  21.   END; $$;

• Dynamic code conversion:

  Oracle

  1. CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE
  2.   AS
  3.   TAB_NAME VARCHAR2(15):='FOR_TYPE';
  4.   TAB_COL VARCHAR2(12):='COL1';
  5.   TAB_COL2 VARCHAR2(12):='COL2';
  6.   COL_VALUE INTEGER:=2000;
  7.   SQL_DELETE VARCHAR2(200);
  8.   BEGIN
  9.   SQL_DELETE:='DELETE '||TAB_NAME||' WHERE ' || TAB_COL2 || '=SYSDATE+5 AND ' ||TAB_COL||' < :value ';
  10.   EXECUTE IMMEDIATE SQL_DELETE USING COL_VALUE;
  11.      
  12.   EXECUTE IMMEDIATE 'begin EXAMPLE_PROC; end;';
  13.   END;

  → PostgreSQL

  1. CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE()
  2.   LANGUAGE plpgsql
  3.     AS $$
  4.     DECLARE
  5.     TAB_NAME  VARCHAR(15) DEFAULT 'FOR_TYPE';
  6.     TAB_COL  VARCHAR(12) DEFAULT 'COL1';        
  7.     TAB_COL2  VARCHAR(12) DEFAULT 'COL2';
  8.     COL_VALUE  INTEGER DEFAULT 2000;
  9.     SQL_DELETE  VARCHAR(200);
  10.   BEGIN
  11.     SQL_DELETE := 'DELETE FROM ' || TAB_NAME || ' WHERE ' || TAB_COL2 || '= LOCALTIMESTAMP+INTERVAL ''5 day'' AND ' || TAB_COL || ' <%1$L ';
  12.     EXECUTE format(SQL_DELETE,COL_VALUE);
  13.          
  14.     EXECUTE  'DO LANGUAGE plpgsql $anonymous_block$ BEGIN    CALL EXAMPLE_PROC(); END; $anonymous_block$';
  15.   END; $$;

• PRAGMA AUTONOMOUS_TRANSACTION conversion:

  Oracle

  1. CREATE PROCEDURE         AUTO_TEST (id_value number, text_value varchar2)
  2.   IS
  3.   PRAGMA AUTONOMOUS_TRANSACTION;
  4.   BEGIN
  5.     INSERT INTO AUTONOMOUS_EVENT (id, value)
  6.     VALUES(id_value, text_value);
  7.     COMMIT;
  8.   END AUTO_TEST;

  → PostgreSQL

  1. CREATE EXTENSION IF NOT EXISTS dblink;
  2.      
  3.   --use superuser to run this script
  4.   CREATE SERVER SWL_targetDBname_link FOREIGN DATA WRAPPER dblink_fdw
  5.   OPTIONS (hostaddr '127.0.0.1', dbname 'targetDBname');
  6.  
  7.   CREATE USER MAPPING FOR targetUser SERVER SWL_targetDBname_link
  8.     OPTIONS (user 'targetUser', password 'pass');
  9.            
  10.   GRANT USAGE ON FOREIGN ERVER SWL_targetDBname_link TO targetUser;
  11.      
  12.   -- procedure code
  13.   CREATE OR REPLACE PROCEDURE AUTO_TEST(id_value DOUBLE PRECISION, text_value VARCHAR,IN is_recursive BOOLEAN DEFAULT false)
  14.   LANGUAGE plpgsql
  15.     AS $$
  16.     DECLARE
  17.     v_sql  text;
  18.   BEGIN
  19.     IF   is_recursive = FALSE THEN
  20.       BEGIN
  21.         IF NOT EXISTS(SELECT 1 FROM DBLINK_GET_CONNECTIONS()
  22.         WHERE dblink_get_connections@> '{myconn}') THEN
  23.           PERFORM DBLINK_CONNECT('myconn','SWL_targetDBname_link');
  24.         END IF;
  25.         v_sql := FORMAT('CALL AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE )',id_value,text_value);
  26.            PERFORM DBLINK('myconn',v_sql);
  27.       END;
  28.     ELSE
  29.     --procedure body
  30.       INSERT INTO AUTONOMOUS_EVENT(ID, VALUE)
  31.     VALUES(id_value, text_value);
  32.          
  33.       COMMIT;
  34.     END IF;
  35.   END; $$;

• XML functions conversion:

  Oracle

  1. with demo1 as(
  2.     select XMLType(
  3.     '<hello-world>
  4.     <word seq="1">Hello</word>
  5.     <word seq="2">world</word>
  6.   </hello-world>
  7.   ') XML
  8.   from dual
  9.   )
  10.   select
  11.     t.xml.extract('//word[@seq=1]/text()').getStringVal() col1
  12.     , decode(t.xml.extract('//word[@seq=1]/text()').getStringVal(), 'Hello', 'Hell', 'END') col2
  13.   from demo1 t

  → PostgreSQL

  1. with  demo1 as(select '<hello-world>
  2.     <word seq="1">Hello</word>
  3.     <word seq="2">world</word>
  4.   </hello-world>
  5.   ':: xml AS XML)
  6.   select(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text AS COL1
  7.     , CASE(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text WHEN 'Hello' THEN 'Hell' ELSE 'END' END AS COL2
  8.   from demo1 T;