Re: mysql to oracle migration

Hi Colm

You might like to look at the Oracle Concepts manual available at otn.oracle.com (free registration required).

Oracle doesn't have an AUTOINCREMENT datatype, you will need to use a sequence and (optionally but preferably) a trigger.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"Colm G. Connolly" <colmconn_at_nowhere.nocountry> wrote in message
news:newscache$et3wsh$hr8$1_at_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      |