Stop losing time and avoid operational costs by moving Oracle to PostgreSQL with Ispirer.
Looking for a reliable and vigorous solution for migrating your database? Let's do it with us!
Benefit from the Ispirer top-notch migration services and robust automated solutions. Foster migration, and optimize the cost and performance of your IT infrastructure by moving your Oracle database to PostgreSQL with Ispirer.
Ispirer Toolkit is a comprehensive solution for database migration. The core of the tool is designed to handle any type of migration regardless of the location of the target database. The tool facilitates the database migration to both on-premises and cloud environments. Ispirer Toolkit effortlessly integrates with popular cloud services including Google Cloud SQL, AWS RDS and Azure Database.
Ispirer Toolkit migrates databases efficiently, minimizing the need for manual corrections. Automate the migration of database schema, business logic, SQL objects, and data in just a few clicks.
If you have your own applications, the Embedded SQL and Database APIs can be converted using either Ispirer Toolkit or Ispirer Service. As a result, they will be able to work with your new PostgreSQL database.
Ispirer has a solution to unlock the full potential of your database. Storing business logic in your database can sometimes impede performance, but Ispirer Toolkit can help you migrate this logic to an application layer seamlessly.
In addition to PostgreSQL, Ispirer can offer you migration from Oracle to other databases, including:
Ispirer Toolkit analyzes all object dependencies during the conversion process and provides not only line-by-line conversion, but resolves type conversions as well. In addition, 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. For more information review the code samples below.
Oracle Collections conversion:
CREATE TYPE employee AS OBJECT ( id NUMBER, Name VARCHAR(300) ); CREATE TYPE employees_tab IS TABLE OF employee; CREATE OR REPLACE PROCEDURE hire(EMPLOYEES IN OUT employees_tab, id NUMBER, Name VARCHAR) AS NEW_EMPLOYEES employees_tab := employees_tab(); BEGIN EMPLOYEES.Extend(1); EMPLOYEES(EMPLOYEES.COUNT) := employee(id, Name); FOR i IN EMPLOYEES.FIRST..EMPLOYEES.LAST LOOP INSERT INTO emp_tab VALUES (EMPLOYEES(i).id, EMPLOYEES(i).Name); END LOOP; INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES); NEW_EMPLOYEES.Extend(EMPLOYEES.COUNT); NEW_EMPLOYEES.DELETE; END;
CREATE TYPE employee AS(id NUMERIC,Name VARCHAR(300)); -- CREATE TYPE employees_tab IS TABLE OF employee; CREATE OR REPLACE PROCEDURE hire(INOUT EMPLOYEES employee[] , id NUMERIC, Name VARCHAR) LANGUAGE plpgsql AS $$ DECLARE NEW_EMPLOYEES employee[] default array[]:: employee[]; BEGIN EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)+1] := null; EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)] := row(id,Name); FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1) LOOP INSERT INTO emp_tab values(EMPLOYEES[i].id, EMPLOYEES[i].Name); END LOOP; INSERT INTO EMP_TAB SELECT * FROM UNNEST(EMPLOYEES); for i in 1 .. coalesce(array_length(EMPLOYEES,1),0) loop NEW_EMPLOYEES[coalesce(array_length(NEW_EMPLOYEES,1),0)+1] := null; end loop; NEW_EMPLOYEES := array[]:: employee[]; END; $$;
Package objects transformation
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "COMMON_PKG" AS CUSTOMER_ID NUMBER(10,0); TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; ADDRES_LST VARCHAR2_AARAY; PROCEDURE PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR2, CUSTTYPE_ID VARCHAR2) IS CUR_ADDR VARCHAR2(500); CUSTM_ID NUMBER; v_int NUMBER; V_RES DBMS_UTILITY.lname_array; l_clob clob; CURSOR cus_addr(cur_id NUMBER) IS SELECT address FROM customer WHERE cust_id = cur_id OR cust_id <5; BEGIN l_clob := ''; SELECT CUST_ID INTO CUSTM_ID FROM customer WHERE CITY = CITY_NAME AND CUST_TYPE_CD = CUSTTYPE_ID AND ROWNUM <= 1; OPEN cus_addr(CUSTM_ID); FETCH cus_addr INTO CUR_ADDR; WHILE(cus_addr%found) LOOP l_clob := l_clob || CUR_ADDR||','; FETCH cus_addr INTO CUR_ADDR; END LOOP; IF cus_addr%isopen THEN CLOSE cus_addr; END IF; DBMS_OUTPUT.put_line( RTRIM(l_clob,',') ); END; PROCEDURE GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE DATE) IS EMPLOYEE_REC EMPLOYEE%ROWTYPE; CURSOR cur_start_date IS SELECT * FROM EMPLOYEE; BEGIN OPEN cur_start_date; FETCH cur_start_date INTO EMPLOYEE_REC; WHILE(cur_start_date%found) LOOP IF EMPLOYEE_REC.START_DATE < STRT_DATE THEN GOTO loop_again; END IF; DBMS_OUTPUT.PUT_LINE(EMPLOYEE_REC.FIRST_NAME || ' ' ||EMPLOYEE_REC.LAST_NAME ); <<loop_again>> FETCH cur_start_date INTO EMPLOYEE_REC; END LOOP; IF cur_start_date%isopen THEN CLOSE cur_start_date; END IF; END; END COMMON_PKG;
CREATE SCHEMA IF NOT EXISTS COMMON_PKG; -- TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; DROP TYPE IF EXISTS COMMON_PKG.GL_VAR_TYPE CASCADE; CREATE type COMMON_PKG.GL_VAR_TYPE as(CUSTOMER_ID BIGINT, ADDRES_LST VARCHAR(50)[]); CREATE OR REPLACE FUNCTION COMMON_PKG.INIT_GL_VAR() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN CREATE TEMPORARY TABLE COMMON_PKG_GL_VAR AS SELECT(array[]:: VARCHAR(50)[]):: COMMON_PKG.GL_VAR_TYPE AS SWV_GL_VAR_VAL; RETURN; EXCEPTION WHEN SQLSTATE '42P07' THEN NULL; END; $$; CREATE OR REPLACE FUNCTION COMMON_PKG.GET_GL_VAR() RETURNS COMMON_PKG.GL_VAR_TYPE LANGUAGE plpgsql AS $$ DECLARE SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE; BEGIN RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR); EXCEPTION WHEN OTHERS THEN PERFORM COMMON_PKG.INIT_GL_VAR(); RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR); END; $$; CREATE OR REPLACE PROCEDURE COMMON_PKG.SET_GL_VAR(SWP_GLVAR COMMON_PKG.GL_VAR_TYPE) LANGUAGE plpgsql AS $$ BEGIN UPDATE COMMON_PKG_GL_VAR SET SWV_GL_VAR_VAL = SWP_GLVAR; END; $$; CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_CUSTID_BY_PARAMS(CITY_NAME VARCHAR, CUST_ID_CD VARCHAR, OUT CUST_ID NUMERIC) LANGUAGE plpgsql AS $$ DECLARE SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR(); IN_ID BIGINT; BEGIN select CUST_ID into STRICT IN_ID from CUSTOMER where CITY = CITY_NAME and CUST_TYPE_CD = CUST_ID_CD LIMIT 1; CUST_ID := IN_ID; END; $$; CREATE OR REPLACE PROCEDURE COMMON_PKG.PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR, CUSTTYPE_ID VARCHAR) LANGUAGE plpgsql AS $$ DECLARE SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR(); CUR_ADDR VARCHAR(500); CUSTM_ID NUMERIC; v_int NUMERIC; V_RES VARCHAR(4000)[] default array[]:: VARCHAR(4000)[]; l_clob TEXT; CUS_ADDR cursor(cur_id NUMERIC) FOR select ADDRESS from CUSTOMER where cust_id = cur_id or cust_id < 5; BEGIN l_clob := ''; select CUST_ID into STRICT CUSTM_ID from CUSTOMER where CITY = CITY_NAME and CUST_TYPE_CD = CUSTTYPE_ID LIMIT 1; open CUS_ADDR(CUSTM_ID); fetch CUS_ADDR into CUR_ADDR; WHILE FOUND loop l_clob := CONCAT(l_clob,CUR_ADDR,','); fetch CUS_ADDR into CUR_ADDR; end loop; if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUS_ADDR') then close CUS_ADDR; end if; RAISE NOTICE '%',NULLIF(rtrim(l_clob,','),''); END; $$; CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE TIMESTAMP) LANGUAGE plpgsql AS $$ DECLARE SWV_GL_VAR COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR(); EMPLOYEE_REC EMPLOYEE%ROWTYPE; CUR_START_DATE cursor FOR select * from EMPLOYEE; BEGIN open CUR_START_DATE; fetch CUR_START_DATE into EMPLOYEE_REC; WHILE FOUND loop << loop_again >> BEGIN if EMPLOYEE_REC.START_DATE < STRT_DATE THEN EXIT loop_again; end if; RAISE NOTICE '%',CONCAT(EMPLOYEE_REC.FIRST_NAME,' ',EMPLOYEE_REC.LAST_NAME); END; fetch CUR_START_DATE into EMPLOYEE_REC; end loop; if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUR_START_DATE') then close CUR_START_DATE; end if; END; $$;
Hierarchical query conversion
CREATE OR REPLACE PROCEDURE sp_hier_with_cte_and_rownum (configList IN VARCHAR2) IS refcur sys_refcursor; BEGIN FOR refcur IN ( WITH test AS (SELECT configList FROM dual) SELECT regexp_substr(configList, '[^;]+', 1, ROWNUM) config FROM test CONNECT BY LEVEL <= LENGTH (regexp_replace(configList, '[^;]+')) +1 ) LOOP DBMS_OUTPUT.put_line('config= '||refcur.config); END LOOP; END;
create or replace Procedure sp_hier_with_cte_and_rownum(IN configList VARCHAR) LANGUAGE plpgsql AS $$ DECLARE refcur REFCURSOR; SWV_REFCUR_rec RECORD; BEGIN FOR SWV_REFCUR_rec IN( WITH RECURSIVE test as(select configList), TabAl_cte AS(SELECT 1 AS LEVEL UNION ALL SELECT TabAl_cte.LEVEL+1 AS LEVEL FROM TabAl_cte, test WHERE(TabAl_cte.LEVEL+1) <= length(regexp_replace(test.configList,'[^;]+','','g'))+1) SELECT SWF_REGEXP_SUBSTR(test.configList,'[^;]+',1,row_number() over()) AS config FROM TabAl_cte,test) LOOP RAISE NOTICE '%',CONCAT('config= ',SWV_REFCUR_rec.config); END LOOP; END; $$;
Dynamic code conversion
CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE AS TAB_NAME VARCHAR2(15):='FOR_TYPE'; TAB_COL VARCHAR2(12):='COL1'; TAB_COL2 VARCHAR2(12):='COL2'; COL_VALUE INTEGER:=2000; SQL_DELETE VARCHAR2(200); BEGIN SQL_DELETE:='DELETE '||TAB_NAME||' WHERE ' || TAB_COL2 || '=SYSDATE+5 AND ' ||TAB_COL||' < :value '; EXECUTE IMMEDIATE SQL_DELETE USING COL_VALUE; EXECUTE IMMEDIATE 'begin EXAMPLE_PROC; end;'; END;
CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE() LANGUAGE plpgsql AS $$ DECLARE TAB_NAME VARCHAR(15) DEFAULT 'FOR_TYPE'; TAB_COL VARCHAR(12) DEFAULT 'COL1'; TAB_COL2 VARCHAR(12) DEFAULT 'COL2'; COL_VALUE INTEGER DEFAULT 2000; SQL_DELETE VARCHAR(200); BEGIN SQL_DELETE := 'DELETE FROM ' || TAB_NAME || ' WHERE ' || TAB_COL2 || '= LOCALTIMESTAMP+INTERVAL ''5 day'' AND ' || TAB_COL || ' <%1$L '; EXECUTE format(SQL_DELETE,COL_VALUE); EXECUTE 'DO LANGUAGE plpgsql $anonymous_block$ BEGIN CALL EXAMPLE_PROC(); END; $anonymous_block$'; END; $$;
PRAGMA AUTONOMOUS_TRANSACTION conversion
CREATE PROCEDURE AUTO_TEST (id_value NUMBER, text_value VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO AUTONOMOUS_EVENT (id, VALUE) VALUES(id_value, text_value); COMMIT; END AUTO_TEST;
CREATE EXTENSION IF NOT EXISTS dblink; --use superuser to run this script CREATE SERVER SWL_targetDBname_link FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'targetDBname'); CREATE USER MAPPING FOR targetUser SERVER SWL_targetDBname_link OPTIONS (user 'targetUser', password 'pass'); GRANT USAGE ON FOREIGN ERVER SWL_targetDBname_link TO targetUser; -- procedure code CREATE OR REPLACE PROCEDURE AUTO_TEST(id_value DOUBLE PRECISION, text_value VARCHAR,IN is_recursive BOOLEAN DEFAULT false) LANGUAGE plpgsql AS $$ DECLARE v_sql text; BEGIN IF is_recursive = FALSE THEN BEGIN IF NOT EXISTS(SELECT 1 FROM DBLINK_GET_CONNECTIONS() WHERE dblink_get_connections@> '{myconn}') THEN PERFORM DBLINK_CONNECT('myconn','SWL_targetDBname_link'); END IF; v_sql := FORMAT('CALL AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE )',id_value,text_value); PERFORM DBLINK('myconn',v_sql); END; ELSE --procedure body INSERT INTO AUTONOMOUS_EVENT(ID, VALUE) VALUES(id_value, text_value); COMMIT; END IF; END; $$;
XML functions conversion
WITH demo1 AS( SELECT XMLType( '<hello-world> <word seq="1">Hello</word> <word seq="2">world</word> </hello-world> ') XML FROM dual ) SELECT t.xml.EXTRACT('//word[@seq=1]/text()').getStringVal() col1 , DECODE(t.xml.EXTRACT('//word[@seq=1]/text()').getStringVal(), 'Hello', 'Hell', 'END') col2 FROM demo1 t
with demo1 as(select '<hello-world> <word seq="1">Hello</word> <word seq="2">world</word> </hello-world> ':: xml AS XML) select(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text AS COL1 , CASE(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text WHEN 'Hello' THEN 'Hell' ELSE 'END' END AS COL2 from demo1 T;
With a proven track record validated by both time and satisfied clients, Ispirer software and services stand as the pinnacle choice for database migration and application modernization. Leveraging years of experience and client feedback, Ispirer guarantees smooth transitions and optimized operational efficiency for businesses of all scales.