Re: Informix to Oracle Migration

philip.hindmoor_at_totalise.co.uk (Phil Hindmoor) wrote in message news:<34549db9.0401230021.7718c5b5_at_posting.google.com>...
> Hi,
> I am sure if anyone can help me, you guys can!
>
> I am an Informix Developer, moving to Oracle 8i and later databases.
> I am struggling to find the Oracle equivelant to many of the useful
> Informix features that I have come to rely upon.
>
> I have looked through the Oracle/Infomix groups and some helpful
> users have posted some solutions, but many of them seem to be very
> complex work-arounds for features that were readily available in
> Informix.
>
> Let me give you some examples:
>
> 1) We regularly used the Informix LOAD FROM and UNLOAD TO statments
> e.g.
>
> UNLOAD TO '/tmp/orderdata.unl'
> SELECT * FROM ORDER
> WHERE val > 100000;
>
> There does not seem to be an easy way to do this in Oracle. Am I
> missing something ( I am sure the answer to that is "Yes"! )

look up the SQl*PLUS SPOOL command.

>
> 2) Informix has a serial data type, so if you had a primary key that
> you would like to auto-increment, you declare it as serial, all done.
> I searched groups and the solution seems to be to create a sequence
> and on insert triggers. Seems a long workaround to me. Is this really
> the recommended way of implementing this behavior in Oracle?

Yes. You got a problem with that? ;^)
>
> If, during testing, you wish to unload some of the tables to file
> and reload them when the tables have been dropped and re-created, it
> seems to be a struggle, as the sequence will generate new key values
> for all the records on insert, and referential integrity is broken.
> Informix's serial datatype does not have a problem with this, if you
> insert a row with a specific value into a serial column, and the value
> is unique, it inserts that value rather than a new serial value, so
> loading old data back into the table is not a problem, integrity in
> maintained.

When you program the trigger to handle that column, you can progrm this way.
IOW, in the trigger, if the value for that column is NULL, then get the next sequence value, else just let the current value be inserted.

Look at the examples again. It's not the problem that you think.
>
> 3) Any user other than the table owner must prefix the table name in
> queries with the owner name, e.g. DEVUSER.ORDER. Why is this?

That's standard SQL

> Would
> any sensible database design allow for two identically named tables in
> the same schema, but with different owners?

Read the manuals about SCHEMA, but I think, your answer  to this question is the expected NO.
 (and I beleive it is also impossible to do.)

> I do appreciate that
> synonyms can be created to work around this, but again this seems to
> be a workaround for a problem that just creates extra hassle and no
> benefit.

You will find much more benefit than hassle to distinct SCHEMAs. Think of it as similar to the difference between programming in BASIC (global variables) and programming in C or PASCAL.

>
> I am sure you guys can help me out with this stuff, it would make my
> professional life a hell of a lot easier if I felt with Oracle, like I
> did with Informix, that I am being assisted by the database, not that
> it is my greatest enemy to hitting deadlines.
>
> Thanks in advance
>
> PH

You are going thru a small culture shock. Much of what you mentioned is just going thru the learning curve. Don't dispair, you'll be fine. Take a class and you'll get comfortable more quickly.

and Welcome to the world of ORACLE!

   Ed
Also checkout comp.databases.oracle.misc and the other 3 ORACLE related groups, since comp.databases.oracle is suposed to go away. (Why GOOGLE still lets me post here is a mystery.) Received on Tue Jan 27 2004 - 19:40:21 CET