INFORMIX Vs.ORACLE

Hi,

This is regarding migration work from informix to Oracle.

INFORMIX 7.X database columns of SERIAL datatype does increment automatically on an insert operation. For eg..

I created a table called Employee. Below are fields created in that.

Emp_id of type 'SERIAL'
Emp_name of type 'CHAR'

I am trying to do some insert operation on this table.

INSERT INTO EMPLOYEE(Emp_name) VALUES ('JAMES') INSERT INTO EMPLOYEE(Emp_name) VALUES ('HARISH')

If I select all from employee table, I see the following output as below.

Emp_id Emp_name
1 JAMES
2 HARISH Here the Emp_id is automatically incremented.

In ORACLE,

I created a table same as employee with following fields:

Emp_id of type 'number'
Emp_name of type 'varchar2'

When I tried to do same insert operation, I get an error. Since in Oracle database column use NUMBER datatype and a sequence for this is created for equivalent to SERIAL in Informix. All the INSERT statements for such tables requires values for the ‘converted serial datatype’ columns in terms of the corresponding '<sequence_name>.nextval’.

create sequence yy ;
INSERT INTO EMPLOYEE(Emp_id, Emp_name) VALUES(yy.NEXT,'JAMES');

Output I see in EMPLOYEE Table is:

Emp_id Emp_name
1 JAMES
2 HARISH Will oracle support having auto incrementing field type. Since we want our client to be database independent, I can't have two different format of insert operation.

Is there any solution, where in ORACLE support auto increment of the number...

Pls. let know.

Thanx in advance,
Harish
email: hpandu_at_miel.mot.com Received on Wed Dec 08 1999 - 12:50:25 CST