As many times
mentioned by Steven Feuerstein and Tom Kyte, there is not much sense to pay for
Oracle DB license and NOT to use SQL and PL/SQL to manage your data - its hard
to imagine better way to do it, plus you've already paid for this.
No matter
what was the reason to start the migration, translation of PL/SQL can be the
biggest challenge.
Here and in next posts I'm going to share my experience and
few solution for migration issues.
Here is an input:
- Oracle to Postgres
- using ora2pg as a tool to translate all the objects including PL/SQL and move the data
- tens of thousands lines of code to move
- small data size
As an output, after running ora2pg, we
have a lot of create statements, some of them running with no problem, rest is
having two types of issues
- compilation issue - you cannot run create statement
- runtime issue - object is created but you have an issue when run procedure or function
Lets consider some:
- Oracle is tolerating data type mismatches - columns with foreign keys, joined columns, parameters of stored programs - in Postgres mismatches can cause an exception
- dynamic sql statements are ignored by ora2pg
- merge statement is not working in some of Postgres versions, but we can use INSERT .. CONFLICT
- LPAD function have different parameter types. Most likely when you see "no such function" error after migration, first thing to check is the parameter order and type
- + operator is not working with date datatype out of the box, intervals can be a solution
