how to get a variable value and update in DB column using sql loader

1
votes

I have a text file with first line as header record,and the remaining lines are details. For the processing, I do not need to store the fields of the header record but I need a date field from this header record and store in as part of the detail record in an oracle record row.I am extracting the details of all rows using 'POSITION' as the file is fixed length

Is it possible to define a variable within the sql loader control file to store the value that I need in memory then use it when I do the sql insert by the sql loader control file

My data file would look like:

193049201209109009238 anjdjtk (Header Record)
1231232 1231386 bkadfjak 989039nnadfsafda(details)
1335635 1237657 lsafnre  234o9034590srgfs(details)

I need to extract position(7:14) from header record and update the a column with this value in Oracle DB

Could someone please help me on this?

1
oraclesql-loader

1 Answers

0
votes

In short, the answer is no. I can think of some alternatives though.

  • Write a PL/SQL program instead of using SQL*Loader. Read each row, save the date from the header, insert along with the detail records.

  • Change the database design to have a header table long with the detail table, then use multiple INTO TABLE clauses along with WITH clauses (assuming there is some way to identify a row as being a header row) to insert into the appropriate table, using a sequence value as a key to join on if there are no other identifying columns that could be used.

  • Preprocess the file in whatever your environment is to get the date from the header row and add it in front of the detail rows, then use SQL*Loader specifying the date as the first column in the control file (skipping loading of the header row then).

  • Load all rows into a staging table, and have a PL/SQL procedure run after it's loaded which would get the date from the header row and use it to write it to the details rows of the main table (kind of like the first alternative).

Let us know what you come up with.