Re: Oracle Migration Approach (Open source vs Vendor Specific)

st 8. 5. 2019 v 17:40 odesílatel Sandeep Saxena <sandeep(dot)lko(at)gmail(dot)com>
napsal:

> Hi All,
>
> In our company we are looking to migrate oracle db to postgres , now since
> we dont have any expertise in Postgres in our organization so we are
> planning to go
> with AWS aurora or EDB postgres so that we have helping hand in
> migration(in dev and dba) while we get mature in postgres ... do you guys
> think after 1-2 years if required it will be easy to move from EDB or
> Aurora to Open source Postgres assuming we dont use AWS services OR would
> you suggest to move to Community version from the start by taking
> support/consultancy from other companies like 2nd quadrant and etc?
>
>
it is hard to say

> All our developers are experienced in pl/sql so I assume it will be easier
> to learn PL/pgsql ,isnt that right? so far only things i dont like about
> PL/pgsql that validation of program units doesnt happen until you run it
> ... though i did find extension plpgsql_check to achieve that to some
> extent.
>

PL/SQL is generic language ADA + SQL. PLpgSQL is reduced PL/SQL with some
more dynamic features. Ada language is generic language with lot of
features that are not practical for usage for stored procedures. PLpgSQL is
reduced language designed specially and only for stored procedures.

PLpgSQL is more simple language than PL/SQL, so it is more easy to use it,
learn it. But it is little bit different language implemented on different
technology - that can be hard to understand. PL/pgSQL and PL/SQL are
similar on top level and very different on low level - some knowledges and
patterns from PL/SQL can be contraproductive - other not.

PLpgSQL does syntax validation at creation time - any other is not possible
because Postgres has not global temporary tables. With late semantic check
PLpgSQL is much more dynamic than PL/SQL. I hope so almost all issues
related to this feature are solved by plpgsql_check (I am a author of
plpgsql_check). I had lot of migration from PL/SQL to PL/pgSQL.

Look on ora2pg. Now it can do lot of good work on automatic migration from
PL/SQL to PL/pgSQL.

Regards

Pavel Stehule

>
> Regards,
> Sandeep
>