Re: Migration from Access/SQL Server to Oracle

And if you create a standard package with a function to get the next value for you, with a side effect of storing it into a package-level variable, then you can also create a function to return that variable's value.

Then, when you create your BEFORE-INSERT triggers, they call the "get next value" function instead of accessing the sequence directly. After the insert has happened, you can call the "get the last inserted value" function, and this will work much like the SQL Server IDENTITY value.

Matthias Gresz wrote:
>
> On Fri, 13 Feb 1998 11:52:29 -0500, "Randy Baker" <rsbakerZ_at_msn.com> wrote:
>
> Hi,
>
> oracle has nothing aquivalent. Use sequences and triggers instaead. Sequqences
> are db-objects that generate unique numbers. Triggers are PL/SQL-commands that
> are executed on certain events.
>
> Example to get field ID behave like an autoincrement field type:
>
> Create Sequence SEQ_Test_ID ;
>
> CREATE TABLE Test(
> ID NUMBER NOT NULL,
> <other columns>
> )
> <storage clause>
> ;
>
> ALTER TABLE Test
> ADD ( CONSTRAINT CON_PK_Test PRIMARY KEY ( ID )
> USING INDEX
> <storage clause>
> ) ) ;
>
> create or replace trigger tIB_Test
> BEFORE INSERT
> on Test
> for each row
> begin
> SELECT SEQ_Test_ID.NEXTVAL INTO :new.ID FROM dual ;
> end;
> /
>
> The trigger tIB_Test will autoincrement pk when inserting rows into the table.
> If you've got to know the pk of the new inserted row for use as fk in a newly
> insrted row of a child table then don't create the trigger, but prefetch the new
> value for use with the insert in table Test and it's child tables.
>
> Something like:
>
> set dsLclNewId=dbMine("Select SEQ_Test_ID.NEXTVAL as NewId from dual",
> 64) '64 mean: SQL-Passthrough
>
> then use the value of the field "NewId" for inserting new parent record
> and as fk for the chield records.
>
> >We have a C++ application which can use either an Access database via DAO or
> >a SQL Server database via ODBC which we would like to migrate to Oracle.
> >
> >Our application makes extensive use of auto-number fields in Access, which
> >map reasonably well to IDENTITY fields in SQL Server. Is there an equivalent
> >in Oracle? (I've seen mention of a "sequence" field in other posts which
> >looks interesting).
> >
> >More importantly, in a few cases these auto-number/IDENTITY fields are used
> >to link records between different tables. Both Access and SQL Server provide
> >a feature for obtaining the last generated number for this purpose. Does
> >Oracle provide a means for doing this via ODBC?
> >
> >Thanks,
> >
> >--
> >Randy Baker (remove Z from address in email replies)
> >
> >
>
> --
>
> Regards
>
> Matthias Gresz :-)
>
> GreMa_at_T-online.de
  Received on Mon Feb 16 1998 - 00:00:00 CST