Oracle to PostgreSQL migration challenges - The Language Differences
Migrating from Oracle to PostgreSQL can be highly appealing for management because it allows for application modernization and compatibility with cloud platforms, in addition to potential savings on licensing costs. However, this transition can prove to be a challenging and time-consuming process for developers and database administrators.
One of the crucial aspects to consider when migrating is the dissimilarity between the PL/SQL language used in Oracle and the PL/pgSQL language used in PostgreSQL. To properly evaluate the complexity and estimate the effort required for your project, it is essential to take note of the following differences in these areas:
1. Legacy Join vs Standard ANSI Join
Legacy or NON-ANSI joins involve utilizing the "WHERE clause" of a SQL statement to merge different datasets. In contrast, the standard ANSI join syntax employs the "FROM clause" of the SQL statement.
Oracle supports both NON-ANSI and ANSI joins, whereas PostgreSQL only supports the SQL Standard ANSI syntax. This straightforward difference has the potential to frustrate developers, especially when dealing with more complex queries.
Legacy Syntax in Oracle
Take a look for the "(+)" symbol it represents a left OUTER join keeping all records for the "emp" table and adding matching records from the "dept" table.
select *
from emp, dept
where emp.deptno = dept.deptno(+)
Standard ANSI Syntax in Oracle and PostgreSQL
The equivalent version in standard ANSI syntax using the FROM clause to describe the relationship between the two tables.
select *
from emp left outer join dept
on(emp.deptno = dept.deptno)
2. Hierarchical Queries
Oracle uses "connect by prior" syntax vs PG uses Subquery Factoring with “recursive” keyword.
Although PostgreSQL is superior in terms of recursive features, it does not directly facilitate a seamless one-to-one migration. Let's consider the equivalent implementation for the well-known organization hierarchy example:
Oracle connect by prior
select
man_id manager_id,
emp_id employee_id,
emp_name employee_name,
substr(
sys_connect_by_path(EMP_ID, '->'),
3
) path
from srcdb.emp
start with man_id IS NULL
connect by prior emp_id = man_id;
PostgreSQL with recursive
with recursive
rec_query (manager_id, employee_id, employee_name, path) as (
select
man_id,
emp_id,
emp_name,
emp_id::text
from emp
where man_id is null
UNION ALL
select
man_id,
emp_id,
rec_query.employee_name,
rec_query.path || '->' || emp_id
from rec_query inner join emp
on(man_id = rec_query.employee_id)
)
select *
from rec_query;
3. Embedded Functions
Oracle offers numerous small built-in functions that can be readily translated into PostgreSQL syntax. However, when dealing with a significant number of embedded functions (where one function calls another function), the complexity of the migration process becomes magnified.
领英推荐
Let's examine the following example, showcasing the two equivalents that implement complex conditional statements.
Oracle Decode function within another Decode function
SELECT MAX(DECODE(
CANDIDATE_ID,
1, 'A',
2, 'BB',
3, 'CD',
4, DECODE(
'var1',
'a', 'a1',
'b', 'b1',
'var111'),
5, 'GD',
'other')) AS COL1
INTO L_ANSWER
FROM SRCDB.CANDIDATES
WHERE CANDIDATE_ID > 5;
PostgreSQL implementation using case when Syntax
SELECT MAX(
CASE
WHEN CANDIDATE_ID = 1 THEN 'A'
WHEN CANDIDATE_ID = 2 THEN 'BB'
WHEN CANDIDATE_ID = 3 THEN 'CD'
WHEN CANDIDATE_ID = 4 THEN
CASE
WHEN 'var1' = 'a' THEN 'a1'
WHEN 'var1' = 'b' THEN 'b1'
ELSE 'var111'
END
WHEN CANDIDATE_ID = 5 THEN 'GD'
ELSE 'other'
END
) AS COL1
INTO L_ANSWER
FROM SRCDB.CANDIDATES
WHERE CANDIDATE_ID > 5;
4. Packages vs Stored procedures
In Oracle, a package is a unique object type that comprises multiple sub-objects, including?
Dividing a package into multiple components is feasible, but it requires careful planning.
Firstly, renaming an object within a package is a complex task as it can potentially lead to naming conflicts with existing objects in the database.
Secondly, conflicts may arise due to interdependencies when referencing objects within the package. Care must be taken to manage these challenges effectively.
Thirdly, variables within Oracle packages are stored in memory and are globally accessible from any other package. Replicating this feature in PostgreSQL may involve creating specific tables to store those variables.?
However, it's important to note that this approach may not offer the same level of performance as Oracle's in-memory variable storage mechanism.
The following package spec (HEADER) in Oracle includes these problematic components:
Oracle Package syntax with all the 5 internal type
create package? ? ? ?pck_test A
? ? TYPE TimeRec IS RECORD (
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? minutes SMALLINT,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? hours SMALLINT);
? ? TYPE TransRec IS RECORD (
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? category VARCHAR2(10),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? account? INT,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? amount? ?REAL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? time_of? TimeRec);
? ? minimum_balance? ? ?CONSTANT REAL := 10.00;
? ? number_processed? ? INT;
? ? no_comm EXCEPTION;
? ? no_sal EXCEPTION;
? ? FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
? ? ? ? ? ? ? ? ? ?manager_id NUMBER, salary NUMBER,
? ? ? ? ? ? ? ? ? ?commission NUMBER, deptno NUMBER)
? ? ? ? RETURN NUMBER;
? ? FUNCTION create_dept(deptno NUMBER, location_id NUMBER)
? ? ? ? RETURN NUMBER;
? ? PROCEDURE remove_emp(empno NUMBER);
? ? PROCEDURE remove_dept(deptno NUMBER);
? ? PROCEDURE increase_sal(p_empno NUMBER, salary_incr NUMBER);
? ? PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
END pck_test;
Possible refactoring snippet for Packages in PostgreSQL
/*** Package Types ***
create type pck_test$TimeRec as
(
minutes smallint,
hours smallint
);
create type pck_test$TransRec as
(
category varchar(10),
account integer,
amount float,
time_of pck_test$TimeRec
);
/*** Package Variables ***/
create table package_variable (
package_schema_name varchar,
package_name varchar,
session_id varchar,
variable_name varchar,
variable_type varchar,
variable_value_txt varchar,
variable_value_dt date,
variable_value_tm time,
variable_value_int integer,
variable_value_num numeric,
variable_value_fl float,
constraint pk_package_variable primary key
(package_schema_name, package_name, session_id, variable_name)
);
-- getter, setter functions
/*** Package Exceptions ***/
raise exception using ERRCODE = -10000, --custom error code,
MESSAGE = 'custom message',
HINT = 'custom hint';
/*** Package Procedures and Functions ***/
CREATE FUNCTION pck_test$create_dept(
deptno numeric, location_id numeric
)
returns numeric
...
CREATE PROCEDURE pck_test$remove_emp(empno numeric)
.../
Overall, when migrating packages from Oracle to PostgreSQL, it is crucial to consider these factors and find appropriate solutions to ensure a smooth and efficient transition.
This is just a small glimpse of the overall picture. If you're seeking more valuable tips or have any questions, feel free to send me a direct message or leave a comment below.