Re: INFORMIX Vs.ORACLE

A copy of this was sent to hpandu <hpandu_at_miel.mot.com> (if that email address didn't require changing) On Thu, 09 Dec 1999 00:20:25 +0530, you wrote:

>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...

create or replace trigger
before insert on employees for each row begin

        select yy.next into :new.emp_id from dual; end;

will do it.

>
>Pls. let know.
>
>Thanx in advance,
>Harish
>email: hpandu_at_miel.mot.com

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 08 1999 - 13:43:34 CST