Re: Migration from Access/SQL Server to Oracle

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/02/16
Message-ID: <6c8t75$l42$4@news00.btx.dtag.de>#1/1

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