I receive records of data to map in tables. The first row contains the header with some informations about the stream of data like the date. I have to save the date and write it in every row. How can I do this? How can I save that information in a variable?
1 Answer
I would write a preprocessor program or script to get the date and add it to every row before loading, but if you are unable to do that there are other alternatives like using external tables or saving the header into it's own table.
In short, create a table to hold the header record. Create a package with a function that sets a package variable to the value of the header date, and a function to return that date. Construct the control file so the header date is the return from the package function that returns the date variable.
I can't take credit for the solution, it's described in the "A SQL*Loader Solution" here: http://allthingsoracle.com/a-tale-of-two-tables-header-rows-in-etl/. It's a clever technique to keep in mind.
