Re: OT: Derby to Oracle
Mike Seda <[email protected]>
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <[email protected]> |
All, My apologies. In the table creation statement, the ID column portion should be changed... FROM: ID NUMBER PRIMARY KEY DEFAULT sometable_sequence TO: ID NUMBER PRIMARY KEY BTW, I borrowed heavily from the following link (and various partners in crime): http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle Thanks Again, Mike Mike Seda wrote: > All, > We came up with the following statements earlier today and forgot to > copy the list: > > CREATE SEQUENCE sometable_sequence > START WITH 1 > INCREMENT BY 1; > > CREATE TABLE somedatabase.sometable ( > ID NUMBER PRIMARY KEY DEFAULT sometable_sequence, > HEADER VARCHAR2 (255) NOT NULL , > LINKTEXT VARCHAR2 (255) NOT NULL , > URL VARCHAR2 (255) NOT NULL , > DESCRIPTION VARCHAR2 (255) NOT NULL , > DUEDATE DATE > ); > > CREATE OR REPLACE TRIGGER sometable_trigger > BEFORE INSERT > ON sometable > REFERENCING NEW AS NEW > FOR EACH ROW > BEGIN > SELECT sometable_sequence.nextval INTO :NEW.ID FROM dual; > END; > > BTW, we also wanted the ID column to be the primary key. > > We have not tried these statements yet though as our Oracle database > is not ready yet. > > Thanks, > Mike > > > Mike Jennings wrote: >> I have also seen the sequence incremented automatically and set on a >> insert using a Oracle Trigger. >> >> On Thu, Apr 16, 2009 at 4:16 PM, Chris Bredesen <[email protected] >> <mailto:[email protected]>> wrote: >> >> Probably what's failing you is the IDENTITY stuff. Oracle doesn't >> have autonumber/identity so you'll have to create a sequence and >> set the default value of the ID column to sequence.nextval. >> >> Also, replace VARCHAR with VARCHAR2. >> >> -CB >> >> >> Mike Seda wrote: >> >> All, >> Does anyone know how to port the following SQL statement (that >> currently works in Apache Derby) to Oracle: >> CREATE TABLE SOMEDATABASE.SOMETABLE ( >> ID BIGINT GENERATED BY DEFAULT AS IDENTITY, >> HEADER VARCHAR(255) NOT NULL , >> LINKTEXT VARCHAR(255) NOT NULL , >> URL VARCHAR(255) NOT NULL , >> DESCRIPTION VARCHAR(255) NOT NULL , >> DUEDATE DATE >> ); >> >> Mike >> >> _______________________________________________ >> Juglist mailing list >> [email protected] <mailto:[email protected]> >> http://trijug.org/mailman/listinfo/juglist_trijug.org >> >> >> _______________________________________________ >> Juglist mailing list >> [email protected] <mailto:[email protected]> >> http://trijug.org/mailman/listinfo/juglist_trijug.org >> >> >> >> >> -- >> Mike Jennings > >