Convert standard SQL files from Oracle to PostgreSQL

[darold]

Convert standard SQL files from Oracle to PostgreSQL

[darold]

Theoretically this could be achieved easily but technically every type of SQL or PL/SQL code processing is separated from others using the export type. This mean that if you want to convert pl/sql code you have to use the FUNCTION, PROCEDURE or PACKAGE export type individually with the corresponding file.

For example, let's say you have a file with several Oracle functions inside, using ora2pg as follow:

ora2pg -p -t FUNCTION -i the_oracle_file.sql -o the_postgresql_file.sql

will do the code conversion. This is the same for PL/SQL packages body, just use -t PACKAGE instead.

Since release 10.0 SQL queries can also be converted by the ora2pg SQL code rewritter with the following command:

ora2pg -p -t QUERY -i the_oracle_file.sql -o the_postgresql_file.sql

This will only apply to SELECT, UPDATE, DELETE and INSERT SQL commands.

So using one file per type of SQL code it is already possible, but I understand the need. Can you send me a sample file ? It will help me to have a random file to parse instead of my own well formatted file and not very useful for others :-)

[vpicavet]

We would need a new export type, mixing function, procedure, package and queries contained in a single SQL file. Would that make sense ?

I will send you a sample file privately, as I cannot share it widely.

[darold]

Yes that the goal of the feature: being able to convert anything from an input file using a new export type.

Of course by "send to me" I wanted to say privately.

[darold]

This item can be closed for me, the feature is now included into Ora2Pg. The only thing is that export must be done separately using the different export type, the all-in-one export in a single output file is not possible with the actual design of Ora2Pg.