Sqlldr holding value

[edog1]

I have a flat file which I am loading into 2 tables, if the record starts with a 1, its a header, if it starts with an 8 its a detail.

Problem is that I need to hold a value from the header to insert with each detail. Here is an example that is what I want to do.

Thanks in advance

load data
REPLACE
INTO TABLE my_table
WHEN (1:1) = '1'
(
PKR_RUN_NUMBER position (12:18) char,
PKR_MODULE_TYPE position (02:03) char,
PKR_PICK_TYPE position (19:20) char,

)
INTO TABLE vms_pick_run_detail
WHEN (1:1) = '8'
(
PRD_RUN_NUMBER PRD_RUN_NUMBER,--Value from header record
PRD_REFERENCE_NUMBER position (60:63) integer external,
PRD_PRIORITY position (97:98) integer external,
)

 

[teado]

I briefly looked at the Oracle Reference Guide, as this interests me as well. Try looking at using the SEQUENCE functionality in the control file. I've not had to write a control file with this, so I don't have any examples. But it looks like you'll have to add a column to the tables you are inserting data to in order to accomodate the sequence.

Once the data is loaded, you should be able to use an update statement to update the detail records with the ID (pkr_run_number being the ID) from the header record.

Good luck.