3 ms·

Someone tell me how to migrate all the PL/SQL I've written and I'll be all ears.

javajosh 8y ago
Yeah, that's a tough problem on two counts. First, it's hard to find SP programmers for any one dialect, let alone two (e.g. PL/SQL and pgSQL). This is why architects who decide to go the SP route must realize that doing so means committing totally to that particular database platform - so you better really like the product, the vendor, and it's support.
iaabtpbtpnn 8y ago
PL/pgSQL is close enough in terms of language features to be nearly syntax compatible after some ora2pg-like transformations, aside from autonomous transactions, which apparently are now supported by Postgres 11 procedures (I haven't tried this). Your main problem will be all the DBMS_* package calls... orafce helps a bit, but not if you've gone really far into Oracle-land. Procedures you wrote for DB system maintenance may not have a meaningful translation. Alternatively, you could pay EnterpriseDB.
irrational 8y ago
I spent a few weeks converting all of ours. At first it was hard trying to figure out the differences, but pretty quickly I was able to get a handle on it and converting them went very quickly. Unfortunately, it has been about 9 months since I did so, so I don't really remember the details of how to go about it.
chasil 8y ago
Worst case scenario, just put all your PL/SQL into a free XE database, then use ODBC to connect to the real data store? It might also be reasonable to migrate what you can into ADA, and run it outside of any database altogether.
kodablah 8y ago
IMO the best practice for starting this migration is dependent upon the caller of the procs. Meaning, where you need to abstract, fork, migrate, write test cases at, etc are at that level. There are too many idiosyncrasies between vendor-dependent plsql to do much more than migrate data and calling code (after having written test cases first to confirm functionality equivalence). Often this can include bidirectional data synchronization between independent DBs during the transition. An apt comparison is a temporary bridge built to serve only during the construction phase of large road projects. Don't be tempted to improve things while migrating, one refactor purpose at a time (accuracy/compatibility, and once complete, then improvements).

Yeah, that's a tough problem on two counts. First, it's hard to find SP programmers for any one dialect, let alone two (e.g. PL/SQL and pgSQL). This is why architects who decide to go the SP route must realize that doing so means committing totally to that particular database platform - so you better really like the product, the vendor, and it's support.

iaabtpbtpnn 8y ago

PL/pgSQL is close enough in terms of language features to be nearly syntax compatible after some ora2pg-like transformations, aside from autonomous transactions, which apparently are now supported by Postgres 11 procedures (I haven't tried this). Your main problem will be all the DBMS_* package calls... orafce helps a bit, but not if you've gone really far into Oracle-land. Procedures you wrote for DB system maintenance may not have a meaningful translation. Alternatively, you could pay EnterpriseDB.

irrational 8y ago

I spent a few weeks converting all of ours. At first it was hard trying to figure out the differences, but pretty quickly I was able to get a handle on it and converting them went very quickly. Unfortunately, it has been about 9 months since I did so, so I don't really remember the details of how to go about it.

chasil 8y ago

Worst case scenario, just put all your PL/SQL into a free XE database, then use ODBC to connect to the real data store? It might also be reasonable to migrate what you can into ADA, and run it outside of any database altogether.

kodablah 8y ago

IMO the best practice for starting this migration is dependent upon the caller of the procs. Meaning, where you need to abstract, fork, migrate, write test cases at, etc are at that level. There are too many idiosyncrasies between vendor-dependent plsql to do much more than migrate data and calling code (after having written test cases first to confirm functionality equivalence). Often this can include bidirectional data synchronization between independent DBs during the transition. An apt comparison is a temporary bridge built to serve only during the construction phase of large road projects. Don't be tempted to improve things while migrating, one refactor purpose at a time (accuracy/compatibility, and once complete, then improvements).