Re: OT: Derby to Oracle

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