0

I'm having difficulty storing an expression to be loaded into two separate columns.

We have two values -- RECORD_ID and ITEM_NUMBER which both need unique values per row.

My first impression was to somehow load the value of the sequence into a boundfiller or something of the like and then use it in both the record id and number, but I'm having difficulty understanding how I would go about that.

Below is a snippet of my current control file I'm using:

...
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(

FIELD1   BOUNDFILLER POSITION(1), -- 
...
FIELD79 BOUNDFILLER,

COLUMN_NAME EXPRESSION ":FIELD1",
ITEM_NUMBER EXPRESSION "???",
...
RECORD_ID "RECORD_ID_SEQ.NEXTVAL"
)

I was hoping I'd be able to do something like

TEMPVALUE BOUNDFILLER "RECORD_ID_SEQ.NEXTVAL"

but haven't found anything similar in nature.

Sed
  • 133
  • 1
  • 1
  • 9
  • I've resolved this issue in this particular instance by setting the item_number to `RECORD_ID_SEQ.NEXTVAL` and record_id to `RECORD_ID_SEQ.CURRVAL`, but am still interested in an answer addressing the problem outlined. – Sed Apr 27 '15 at 13:53

1 Answers1

0

I believe something like that belongs in a BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER tablename_BI
BEFORE INSERT ON tablename
FOR EACH ROW

BEGIN
  :new.record_id := RECORD_ID_SEQ.NEXTVAL;
END;
Gary_W
  • 9,933
  • 1
  • 22
  • 40