Informix to Oracle Migration

[Phil Hindmoor]

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"! )

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?

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.

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? Would
any sensible database design allow for two identically named tables in
the same schema, but with different owners? 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.

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

[Ed prochak]

ph*************@totalise.co.uk (Phil Hindmoor) wrote in message news:<34**************************@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.)