Re: Packages: Again

Le 11/01/2017 à 20:32, Pavel Stehule a écrit :
>
>
> 2017-01-11 19:57 GMT+01:00 Joshua D. Drake <jd(at)commandprompt(dot)com
> <mailto:jd(at)commandprompt(dot)com>>:
>
> -hackers,
>
> I know we have talked about this before but today it was impressed
> upon me rather firmly. I presented a Webinar: Postgres for Oracle
> People. The attendees were 90% pl/pgsql developers. 330 people
> registered for an event that was only allowed to host 100 people.
> The webinar went on for 2 hours. (it was only scheduled for one
> hour, that is how interactive it was)
>
> By far the tagline of this webinar from attendees was, "We can not
> port without packages"
>
> So this is a reality. If we want tried and true Oracle developers
> to port to PostgreSQL, we must provide some level of package
> capability.
>
> There are some that would say we don't need them. You are right,
> we don't need them. We should however want them if we want to
> continue to stomp through the business sector and continue growth.
>
>
> We have a schemas instead - the PostgreSQL schema is close to Oracle
> packages.
>
> What we cannot to substitute are package variables, now - see my
> proposal for session variables.
>
> Now I am working on migration some large Oracle project - I see more
> significant issues
>
> 1. no good tools - ora2pg do lot of work, but the PL/SQL -> PL/pgSQL
> migration support is basic
> 2. some things in Postgres are different - boolean type, enum types,
> date type, OUT parameters ..
> 3. some things are really different - NULL versus empty string
> 4. there are not good tools for postprocessing PL/pgSQL beautifier
> (formatter), SQL formatter
> 5. The developers still using Oracle outer joins - there are not 100%
> automatic migration
> 6. missing some common patterns for deployment, tests for really big
> set of code.
>
> Now I work on migration about 500K rows - and it is terrible work. It
> is 20 years old project - lot of code is not clean, It is hard to
> migrate, it is hard to clean. Sure, there is not one line of tests.
>
> If we miss some, then it is modern robust tool for migration - big
> thanks to ora2pg maintainers and developers - without it, there is
> nothing free.
>
> Regards
>
> Pavel

Hi,

I'm currently working on release 19.0 of Ora2Pg, I hope to get it out
this weekend. This release has a major rewrite of the pl/psql rewriter.
Some of the issues you've reported to me Pavel are already solved in
this branch, some other have been fixed after your reports. The rewriter
has no more limitation in rewriting function call like decode(),
previous version was failing to rewrite function call when an other
function or sub select was called inside.

Ora2Pg has always used schema to replace package and I think it is the
good equivalent to Oracle's package, we don't need more. The only thing
that is missing are global variables. Release 19.0 of Ora2Pg will try to
address this problem by exporting global variables declared into the
package as PostgreSQL user defined custom variable and replace all call
to these variables in the plpgsql code by

current_setting('schema_name.var_name')::var_type

and all affectation of these variables by

SELECT / PERFORM set_config('schema_name.var_name', var_value, false);

This works great but the only difference with Oracle's global variables
is that they are visible outside the schema where, in Oracle, they are
only visible in the package scope when declared in the package body.
Perhaps we can work on having these custom variables visible only in a
particular schema or some other mechanism that made them accessible from
the plpgsql code only. Ora2Pg doesn't propose any solution to cursors
declared as global variable yet.

Ora2Pg can fully rewrite Oracle's function with autonomous transaction
using a wrapper with a call to dblink or pg_background if you enable it.
It's just works perfectly.

About Oracle's OUTER JOIN notation, (+), next release of Ora2Pg will be
able to rewrite simple form of RIGHT OUTER JOIN, LEFT OUTER JOIN will
comes soon. Note that there is no automatic tool to rewrite the Oracle
outer join syntax, but you can use TOAD to convert the queries into ANSI
syntax unfortunately query per query and manually. Next version will
also add better support to export Oracle Text Search indexes using
pg_trgm, unaccent and FTS.

Including a SQL and plpgsql code beautifier is also in my todo list,
probably available in next major version 20.

In my opinion, Ora2Pg can do more automatic works but it need some more
developments. I would like to give all my time to improve this project
and give you a better tool but this is not really possible for the
moment and unfortunately my spare time is not extensible. I'm doing my
best to get out more releases, your reports/feedbacks help me a lot to
add more automatic migration code. I don't think it is possible to have
a 100% automatic migration because there will always be some things that
need manual rewrite, like point 3, I don't think we want stuff like NULL
equal EMPTY. There is also tons of external modules like DBMS_* that can
be compared to extension, but if every one share is work on migration
perhaps we can save more of time.

Regards,

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org