Re: OT: Derby to Oracle
Chris Bredesen <[email protected]>
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <[email protected]> |
Good luck with it, Mike. And when you're ready to stop messing around with portability, there's always Hibernate :) http://www.hibrnate.org (disclosure: I work for JBoss (Red Hat), in case that wasn't clear from my email addy) -Chris Mike Seda wrote: > 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 >> >> >