Migrating from one database system to another is not an easy task. It might be easy if you only need to migrate the data, but as soon as you have business logic in the database this can become a lot work quite fast. When you want to migrate from Oracle to PostgreSQL this means rewriting all your PL/SQL code into something which PostgreSQL understands, and this usually is PL/pgSQL as it is very close to PL/SQL. Some might say that you mustn’t put any business logic into the database at all, but this is another discussion and will not be handled here or in any of the follow posts.
There are some tools, such as ora2pg, which give you some automatic translation from PL/SQL to PL/pgSQL, but you carefully have to check the result and go through all the code anyway. ora2pg gives you a great starting point, but the works begins after the automatic translation.
In this first post, we’ll look into the very basics of both languages. The starting point in Oracle is this:
sqlplus [email protected]/XEPDB1 as sysdba
create user u
identified by u
default tablespace users
quota 10M on users
temporary tablespace temp;
grant create session to u;
grant create table to u;
grant create procedure to u;
exit;
sqlplus [email protected]/XEPDB1
drop table t2;
drop table t;
create table t ( a number primary key , b number check ( b> 10 ));
create table t2 ( a number primary key, b number references t(a) );
insert into t (a,b) values (1,11);
insert into t (a,b) values (2,12);
insert into t2 (a,b) values (1,1);
insert into t2 (a,b) values (2,1);
insert into t2 (a,b) values (3,1);
insert into t2 (a,b) values (4,1);
insert into t2 (a,b) values (5,1);
insert into t2 (a,b) values (6,2);
commit;
This gives you a dedicated user which enough permissions to connect, create tables and procedures. This user is then used to create two simple tables connected by a foreign key constraint. While this is really simple, the following (and all follow up) examples are redacted and simplified real customer use cases.
The procedure to be migrated is this one:
create or replace procedure p as
ln_count number;
begin
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Disabled foreign keys constraints');
select count(*)
into ln_count
from t2
where b = 2;
-- some more logic around counting
--
delete
from t
where a = 2;
delete
from t2
where b = 2;
commit;
dbms_output.put_line ( 'Commit done');
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Enabling foreign keys constraints');
exception
when others then
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Enabling foreign key constraints');
raise_application_error(-50001, 'runtime_exception', true );
end;
/
Again, this is very simplified and there was more code in the real procedure, but this is enough to talk about the topic of this post.
When this procedure is executed we get the following result in Oracle:
SQL> select * from t;
A B
---------- ----------
1 11
SQL> select * from t2;
A B
---------- ----------
1 1
2 1
3 1
4 1
5 1
The starting point in PostgreSQL is this:
$ psql
create user u with login password 'u';
create schema u;
alter schema u owner to u;
\c postgres u
set search_path=u;
create table t ( a int primary key, b int check ( b > 10 ));
create table t2 ( a int primary key, b int references t(a));
insert into t (a,b) values (1,11);
insert into t (a,b) values (2,12);
insert into t2 (a,b) values (1,1);
insert into t2 (a,b) values (2,1);
insert into t2 (a,b) values (3,1);
insert into t2 (a,b) values (4,1);
insert into t2 (a,b) values (5,1);
insert into t2 (a,b) values (6,2);
… which gives you pretty much the same as in Oracle (except for the schema and integer data types).
Let’s start with migrating that procedure, step by step. The “create procedure” command in PostgreSQL works like this (there is a bit more, but it is the simplest form to start with):
create procedure p() as
$$
declare
begin
null;
end; $$ language plpgsql;
PostgreSQL uses dollar quoting which saves you from constantly escaping special characters in strings, e.g.:
postgres=> select $$""""'''''%%%&&&&&$$;
?column?
-------------------
""""'''''%%%&&&&&
(1 row)
If you have the “$” sign itself in the string you can use tags ($xx$ in the example below):
postgres=> select $xx$""""$$'''''%%%&&&&&$xx$;
?column?
---------------------
""""$$'''''%%%&&&&&
(1 row)
The same concept is used for the body (code) of user defined functions and procedures.
“language” tells PostgreSQL which language you’re writing the code in, here we use PL/pgSQL but this could also be Python, Perl or Tcl in a typical PostgreSQL installation (more languages are available as external extensions).
The “declare” block is used to declare variables you want to use in the code (more on that in a later post) and “begin” and “end” wraps the actual code.
Now, that we know the basic building blocks we can go further and start to code the procedure so that it is doing the same as the procedure in PL/SQL. This is the topic for the next post.