Migrate Oracle to PostgreSQL

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.

Migrate Oracle to PostgreSQL

Chip

Ispirer Toolkit for automated migration

Ispirer's Oracle to PostgreSQL migration tool automates migration of Oracle to PostgreSQL and you get:

  • Free assessment tool to analyze your database and estimate migration complexity
  • Assistance in Ispirer Toolkit configuration
  • Automated migration of the entire database schema, tables, SQL objects, business logic, and data with Ispirer Toolkit
  • Timely customization of Ispirer Toolkit to maximize automation rate
  • Expert support during the entire project

Check all the features by requesting a free trial of Ispirer Toolkit.
It is valid for 30 days.

Free trial

People

Turnkey migration service

End-to-end migration service provided by Ispirer professionals includes:

  • Migration of data and database schema, including SQL objects and tables
  • Team of Oracle/PostgreSQL experts and dedicated Project Manager
  • Detailed analysis of your database and tailored migration roadmap
  • Regular updates on the project status
  • Post-migration refinement and testing

The cost of our services depends on a number of factors.
Let's discuss your Oracle to PostgreSQL conversion project!

Request a quote

Ispirer Toolkit for Automated Migration

SQLWays Wizard Logo

Ispirer Toolkit is a robust solution for automated heterogeneous database migration and application conversion. Using this tool, you can transfer not only tables and data, but also stored procedures, functions, packages, views, and triggers. This solution is based on an intelligent proprietary algorithm that analyzes data types, relationships between objects, reserved words, and even code structures that do not have equivalents in a target technology.

Ispirer Toolkit supports both legacy and most popular RDBMS, such as Informix, Sybase, DB2, Teradata, Firebird, Progress, Oracle, Microsoft SQL Server, PostgreSQL, MySQL. To ensure a smooth migration process, our support experts are available to assist with tool configuration or resolve any issues in a timely manner.

High quality

High quality SQL code conversion

Expert system with 20.000+ conversion rules and 100.000+ automated tests.

Flexibility

Flexibility

Nimble configuration with 300+ parameters and options for SQL objects and data multithread migration.

Free smart assessment

Free smart assessment

Ispirer's free Assessment Wizard for migration scope and complexity evaluation.

Intuitive and instructive reports

Comprehensive migration analysis

Intuitive and instructive reports for cost-effective post-migration polishing.

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.

Google Cloud SQL

AWS RDS

Azure Database

Oracle to PostgreSQL Migration Demo

Check out how you can automatically convert Oracle to PostgreSQL database with the help of Ispirer Toolkit.


Drop us a line, if you have any business inquiries or questions regarding your Oracle to PostgreSQL conversion project. Ispirer experts will provide support and answer any questions you may have!

Contact us

Migration Overview

Database Migration

Ispirer Toolkit is a comprehensive migration solution that helps to convert Oracle to PostgreSQL automatically. Both conversion with connection to the source database, as well as conversion of files containing PL/SQL scripts are possible. The first option is most recommended, as the tool will get all the required information about object dependences directly from the database. Ispirer Toolkit can migrate the following objects: tables, views, functions, procedures, packages (specification and body), triggers, sequences, collection and object types. As a result, each separate database object is converted to its equivalent in PostgreSQL.

Table and Data Conversion
Ispirer Toolkit converts tables with all related objects (indexes, referential integrity constraints) and data to the target PostgreSQL database. All Oracle native data types are converted to the PostgreSQL equivalent. If necessary, the user can change the default settings of data type mapping.

Procedure and Function Conversion
Oracle user procedures and functions are converted to stored procedures and functions, written in PostgreSQL syntax, depending on the version of the target database. Packages are migrated to a separate set of objects - procedures and functions. Oracle to Postgres migration require package-level variables and constants to be converted as well. Ispirer Toolkit converts them to tables that store the actual values, and a set of functions that define and get values of those tables. Oracle built-in functions and procedures are converted to their equivalents in PostgreSQL. If there is none, the tool will try to convert the functions to PostgreSQL expressions. They will emulate the same behavior or generate a user-defined function that will work the same way as in the Oracle database.

Trigger Conversion
Ispirer Toolkit automatically migrates Oracle triggers to the trigger function, and the trigger itself to the PostgreSQL database. Oracle trigger logic is moved to the trigger function as well. The tool then generates a trigger that calls this trigger function.

Working with Collections and their Methods
Ispirer Toolkit supports collection conversion of all three types: Associative array, Nested table, Variable-size array (varray). They are converted to a PostgreSQL table. Then a set of user-defined functions is generated. This allows to work with values in such tables.

Alias Conversion
The Ispirer Toolkit migrates Oracle synonyms to the original object names in the places where they are used in the PostgreSQL code. This is the default behavior of the tool. Moreover, if the Oracle database has synonyms only for tables, Ispirer Toolkit can convert them to views by setting the appropriate option.

Oracle Supplied PL/SQL Package Conversion
Ispirer Oracle to PostgreSQL converter supports some Supplied PL/SQL Packages, such as UTL_FILE, DBMS_LOB and others. The list can be expanded depending on the requirements of your conversion project.

If you have your own applications, the Embedded SQLand Database APIscan be converted using either Ispirer Toolkit or Ispirer Service. As a result, they will be able to work with your new PostgreSQL database.

Migrate Logic From Database to Application

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.

    BL Migration
  • Automated conversion of SQL objects to application code. Ispirer Toolkit supports Oracle, SQL Server, Sybase, PostgreSQL, Java, C#, Python, etc.
  • In case of changing the RDBMS, Embedded SQL will be automatically rewritten to adhere to the syntax of the new database.
  • Migration with\without connection to databases. Ispirer Toolkit can work with or without a database connection. Both options allow you to successfully migrate business logic from the database to the application layer. However, it is worth considering that connecting to the database allows Ispirer Toolkit to take into account additional data from the database and helps improve conversion.

In addition to PostgreSQL, Ispirer can offer you migration from Oracle to other databases, including:



Turnkey Migration Service

Choose Ispirer as a partner who not only understands the need for database and application modernization, but also has in-depth technical expertise in the migration field, and the solutions and methodologies to guide you along this journey. It is important to have a migration plan that matches specific business goals of your organization in order to successfully implement your software modernization project.

Together with the Ispirer team, you will be able to avoid pitfalls and achieve the optimum outcome in the most cost-effective manner. We will be in touch with you throughout the migration process, from initial preparation and assessment to automated schema conversion, post-conversion refinement and testing ensuring the transparent process of the Oracle to PostgreSQL conversion.

Services general

Looking for Oracle to PostgreSQL Migration Services?

Drop us a line and we’ll estimate your project for free!

Request a quote

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. 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:

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

Package objects transformation

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

Hierarchical query conversion

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

Dynamic code conversion

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

PRAGMA AUTONOMOUS_TRANSACTION conversion

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

XML functions conversion

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

Download Sample Code

You have just examined only a piece of the Oracle to PostgreSQL conversion sample. You can download the entire database migration sample for free by filling out the form. Once you get the code sample, you will be able to:

  • Explore how well Ispirer Toolkit migrates the source code to the target technology without downloading it.
  • Analyze and compare the source sample with your code to understand which components of your database can be automatically converted using Ispirer Toolkit.
  • Run the database and insert the code to ensure it works properly.

See for yourself that Ispirer Toolkit can automatically convert not only a single piece of code, but an entire application. Most likely, complex code will require customization of the toolkit, but our experts can add new conversion rules within 3-5 business days. As a result, using Ispirer Toolkit you will get readable and maintainable code of manual conversion quality in the shortest time possible.

Snatch a totally free opportunity to see the high efficiency of Ispirer Toolkit!

Get your sample code
Enter your name.

Enter a valid e-mail address.

Select your country.

Invalid input.

Our Projects

As part of the cloud transition project, getting rid of an outdated OS/390 mainframe allowed a global HR software vendor to gain security and manageability with the latest Microsoft business solutions.

Replacing Db2 with a PostgreSQL database enabled the innovator in business payment automation technologies to save on licensing, support, and maintenance costs.

Using PostgreSQL, the global assurance and consulting leader has minimized system downtime, improved their infrastructure performance and reliability.

previous arrow
next arrow
Slider

Why Migrate with Ispirer

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.

  • Seasoned team. Ensuring high security and performance standards is what we do best, thanks to our impressive experience in building reliable and scalable solutions.
  • Technology expertise. Having 20+ years of experience our team has gained a wide pool of expertise in various programming languages from the rarest to the most popular ones.
  • Top-notch data security. We comply with ISO 27001 security management requirements with comprehensive policies and processes, advanced security technology, and skilled professionals.
  • Proprietary tools. In migration projects we employ Ispirer proprietary tools which underscores our dedication to delivering solutions of utmost reliability and performance. Compiled on a daily basis, this toolkit continually integrates dozens of new conversion rules, thereby consistently enhancing the automation capabilities. Find out more about our solution in the infographic:
Infographics showing how automatic application and database migration brings benefits

What Our Customers Say

"Thanks to Ispirer for providing such a useful tool for database migration projects and I would recommend to anyone who has the same kind of project to use the tool to save effort/cost and reduce risks/challenges."

"Because we have hundreds of databases that need to be migrated, automating the whole process was key for us. Ispirer helped us achieve just that, through their excellent tooling, customizations, and support."

"Ispirer Toolkit is well worth the investment and I would use it again in a similar case. I must commend the Ispirer team for their responsiveness, professionalism and willingness to help."

"The team has now used Ispirer for multiple use cases and has saved significant time and effort. The experience with Ispirer has been great, from the software, support, and communication."

"SCC Soft Computer was extremely happy with the professionalism, timeframes, milestones and output from the engagement. SCC Soft Computer would not hesitate to re-engage Ispirer Systems for future engagements."

"We are very pleased working together with Ispirer as a team. They proved to be a reliable solution partner providing a great service. And we would recommend Ispirer to anyone having a similar conversion project."

previous arrow
next arrow
Slider

Get in touch with us!

Have questions about migration from Oracle to PostgreSQL?

Contact us