ansaurus

Question

Answer 1

+5 A:

1: You'll have to create a sequence and a trigger

 CREATE SEQUENCE MyCountIdSeq;
 CREATE TABLE MyCount (
     Line INTEGER NOT NULL,
     ...
 );
 CREATE TRIGGER MyCountInsTrg BEFORE INSERT ON MyCount FOR EACH ROW AS
 BEGIN
     SELECT MyCountIdSeq.NEXTVAL INTO :new.Line
 END;
 /

2: BLOB.

erikkallen 2009-03-26 09:59:02

sequence and triggers are the safer option for managing keys. you can also use them to maintain update dates and updaters.

Martlark 2009-03-26 10:46:00

I think it usually a good idea to check for any existing value in the trigger just in case you wanted to manually insert a record at some point or had to do bulk inserts with predefined ids.

DrJokepu 2009-03-26 16:45:50

Well, if you disabled the trigger while inserting a row, I'd say it's your problem. Also, if you run a batch with triggers disabled for performance, better update the sequence to reflect this.

erikkallen 2009-03-27 21:14:53

Answer 2

+4 A:

You don't need to use triggers for this if you manage the inserts:

CREATE SEQUENCE seq;

CREATE TABLE mycount
(
   line NUMBER(10,0)
);

Then, to insert a value:

INSERT INTO mycount(line) VALUES (seq.nextval);

For images, you can use BLOBs to store any binary data or BFILE to manage more or less as a BLOB but the data is stored on file system, for instance a jpg file.

References:

FerranB 2009-03-26 10:10:18

+1 for avoidance of triggers.

darasd 2009-03-26 10:14:37

it is surely past time oracle brought in auto increment keys.

Martlark 2009-03-26 10:45:07

thank you very very much, it was useful

Gold 2009-03-26 11:38:41

ansaurus

tags:

views:

answers:

Translating SQL for use with Oracle

related questions