Migration from Postgresql 9 to Oracle 10g

[Shams Khan]

Hi Experts,

I am in the process of migration from PG 9 to oracle. All of the
database objects has been migrated apart from functions. The main
issue I am facing with function code where DML statements are used in
the code. On the other side, Oracle doesn't support DML's in functions
or procedures. I used ESF Database Migration tool in order to convert
and does some remaing task manually. Is there any tool by which can
convert functions as well?

Regards,
Shams

[Albe Laurenz]

Maybe you will get more enthusiastic support in an Oracle forum...

It is curious that you want to migrate form a fairly
current PostgreSQL version (you didn't say which one) to
an Oracle version that is already out of (Premier) support
and will be terminally abandoned next year.

Post by Shams Khan
On the other side, Oracle doesn't support DML's in functions
or procedures.

That's not true.
You can use INSERT, UPDATE and DELETE in PL/SQL.
You can do about anything in PL/SQL.

Yours,
Laurenz Albe

[Gabriele Bartolini]

Hi Shams,

Post by Shams Khan
I am in the process of migration from PG 9 to oracle. All of the
database objects has been migrated apart from functions. The main
issue I am facing with function code where DML statements are used in
the code. On the other side, Oracle doesn't support DML's in
functions
or procedures. I used ESF Database Migration tool in order to convert
and does some remaing task manually. Is there any tool by which can
convert functions as well?

I guess you are better off asking this to an Oracle forum or to Oracle
itself (or consultants).

Here I am sure you can find a lot of people (including myself) who are
really eager to talk about the inverse process and who are specialised
with that. We - for instance - manage Oracle to vanilla PostgreSQL
migrations with semi-automated tools able to convert PLSQL functions,
but not the other way around. Sorry. :(

Just for the record and to understand if this is due to PostgreSQL
limitations, are you in a position to give us technical/product reasons
for this decision?

Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
***@2ndQuadrant.it - www.2ndQuadrant.it

[f***@frank.uvena.de]

Post by Gabriele Bartolini
Just for the record and to understand if this is due to PostgreSQL
limitations, are you in a position to give us technical/product
reasons for this decision?

I'd like to also know reasons for this....

Cheers,
Frank

[m***@trausch.us]

Post by f***@frank.uvena.de

Post by Gabriele Bartolini
Just for the record and to understand if this is due to PostgreSQL
limitations, are you in a position to give us technical/product
reasons for this decision?

I'd like to also know reasons for this....

I'll admit, I'm curious, too.

I've helped people move from all sorts of databases to PostgreSQL over
the years, and usually the major concerns are getting away from
proprietary/expensive/insecure/unsafe and/or discontinued systems,
Oracle included in that.

--- Mike

[Grant Allen]

Post by Shams Khan
Hi Experts,
I am in the process of migration from PG 9 to oracle. All of the
database objects has been migrated apart from functions. The main
issue I am facing with function code where DML statements are used in
the code. On the other side, Oracle doesn't support DML's in functions
or procedures.

Er, what? What exactly do you mean "Oracle doesn't support DML's in ... procedures"? Several million Oracle developers will be shocked to hear they've been writing phantom code updating, inserting, deleting etc. in their PL/SQL. Assuming that's what you mean by DML. Can you provide an example?

And +1 to the other comments on being curious about the motivation for the migration in this direction.

Ciao
Fuzzy
:-)

[Craig Ringer]

Post by Grant Allen

Post by Shams Khan
Hi Experts,
I am in the process of migration from PG 9 to oracle. All of the
database objects has been migrated apart from functions. The main
issue I am facing with function code where DML statements are used in
the code. On the other side, Oracle doesn't support DML's in functions
or procedures.

Er, what? What exactly do you mean "Oracle doesn't support DML's in ... procedures"?

I'm assuming the intention was to write "DDL".

--
Craig Ringer

[Grant Allen]

Post by Craig Ringer

Post by Grant Allen

Post by Shams Khan
Hi Experts,
I am in the process of migration from PG 9 to oracle. All of the
database objects has been migrated apart from functions. The main
issue I am facing with function code where DML statements are used in
the code. On the other side, Oracle doesn't support DML's in functions
or procedures.

Er, what? What exactly do you mean "Oracle doesn't support DML's in ... procedures"?

I'm assuming the intention was to write "DDL".

Ah. Well, not as plain statements - very true. But wrap them in "execute immediate" and one is good to go :)

But we digress ... I'm still fascinated to find out what's driving the move away from Postgres.

Ciao
Fuzzy
:-)