Обсуждение: Migration from Postgresql 9 to Oracle 10g

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

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.

Shams Khan wrote:
> 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

 Hi Shams,

 On Thu, 18 Oct 2012 11:43:52 +0530, Shams Khan <shams.khan22@gmail.com>
 wrote:
> 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
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

Am 2012-10-18 10:31, schrieb 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

On 10/18/2012 05:02 AM, frank@frank.uvena.de wrote:
> Am 2012-10-18 10:31, schrieb 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

--
A man who reasons deliberately, manages it better after studying Logic
than he could before, if he is sincere about it and has common sense.
                                   --- Carveth Read, “Logic”

On 18/10/2012 02:13, Shams Khan wrote:
> 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
developerswill 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
:-)

On 10/19/2012 01:46 AM, Grant Allen wrote:
> On 18/10/2012 02:13, Shams Khan wrote:
>> 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

On 18/10/2012 23:31, Craig Ringer wrote:
> On 10/19/2012 01:46 AM, Grant Allen wrote:
>> On 18/10/2012 02:13, Shams Khan wrote:
>>> 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
:-)