Re: mysql to oracle migration

From: Colm G. Connolly <colmconn_at_nowhere.nocountry>
Date: Tue, 10 Feb 2004 13:38:47 +0000
Message-ID: <newscache$et3wsh$hr8$1@weblab.ucd.ie>

Kris Jenkins wrote:

> Colm G. Connolly wrote:
>

>> Hi all,
>> 
>> I'm in the process of migrating to oracle (9.2.0.1.0) from Mysql and am
>> having a few problems. Perhaps somebody might know how to solve them.
>> 
>> 1. When I create my tables I try to create a column of boolean type but
>> for some reason oracle doesn't like it. Should I just number(1) instead?
>> (I've tried inhibitoryShunt boolean and inhibitoryShunt boolean default
>> false)

>
> Hmm...pretty sure there isn't a Boolean data type in SQL.
>

>> 2. What are oracle equivalents of the MySql functions now() and
>> last_insert_id()? I use them in the following contexts.

>
> The equivalent of now() is sysdate. It doesn't need (or want) brackets,
> as it's considered a keyword rather than a function.

Great that works a treat, thanks.

>
> What does last_insert_id() do in mysql? Is it an OID thing?

From the mysql docs....
LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

For example assuming that configurationMap and tableMap are both empty then in the following code last_insert_id() would return 1.

insert into configurationMap values (NULL, 'MoreInhibition', now(), null);

insert into tableMap values

        (NULL, last_insert_id(), 'afferentSynapses',             last_insert_id()),
        (NULL, last_insert_id(), 'lateralConnectionsParameters', last_insert_id()),
        (NULL, last_insert_id(), 'networkParameters',            last_insert_id()),
        (NULL, last_insert_id(), 'excitatoryNeurons',            last_insert_id()),
        (NULL, last_insert_id(), 'inhibitoryNeurons',            last_insert_id()),
        (NULL, last_insert_id(), 'lateralExcitatorySynapses',    last_insert_id()),
        (NULL, last_insert_id(), 'lateralInhibitorySynapses',    last_insert_id());

One more thing, I have created a table as follows (in oracle)

create table configurationMap (
pk number not null primary key, --auto-increment configurationName varchar(50) not null,
creationDate date not null,
useDate date
);

How do I insert into the table in such a way that pk in each row is automatically incremented? In mysql I can simply do "insert into configurationMap values (NULL, 'MoreInhibition', now(), null);" and it will automatically place the correct value into the pk column even though it was marked decclared as non null.

Thanks,

-- 
                             _\\|//_ 
                             ( O-O )
---------------------------o00--(_)--00o------------------------------
Colm G. Connolly                | 
Department of Computer Science  | 
University College Dublin (UCD) |
Belfield, Dublin 4              | 
Éire / Republic of Ireland      | 

Received on Tue Feb 10 2004 - 07:38:47 CST