| SQL*Loader User Defined Variables [message #74323] |
Mon, 15 November 2004 05:49  |
Andy Williams
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Hi,
the first header record of a flat file which I wish to not to load has a date field which I need to use a transaction date in the records I will load.
I cannot find any referencing to the use of internal variables or substituition variables.
My colleague has got round this by creating another table specifically for the header record and using triggers and package variables to populate the loaded records with this date. I think this is over the top.
Can anyone suggest anything else that may help?
Thx,
Andy
|
|
|
|
| Re: SQL*Loader User Defined Variables [message #74324 is a reply to message #74323] |
Mon, 15 November 2004 14:38  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
you can easily generate the .ctl file on the fly (using echo or prompt commands in sqlplus or shell) having the constant you want and then just use that .ctl file to load the data.
You can also just get the date from the first line into a variable and then create a secondary manipulated datafile and load that. Here is a simple example of adding the system date to data and swapping the columns:
$>cat t.txt
01 data02
11 data12
21 data22
$>export DT=`date`
$>cat t.txt | awk '{print $2, $1,"'"$DT"'"}' > t2.txt
$>cat t2.txt
data02 01 Mon Nov 14 15:32:27 PST 2004
data12 11 Mon Nov 14 15:32:27 PST 2004
data22 21 Mon Nov 14 15:32:27 PST 2004
|
|
|
|
| Re: SQL*Loader User Defined Variables [message #74330 is a reply to message #74324] |
Tue, 16 November 2004 05:20 |
Andy Williams
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Thanks for that, but i'm talking about manipulating values within sqlloader at runtime for the same file
i.e.
HDR20041010
REC1 NAME
REC2 XXXX
REC3 YYYY
I want to ignore the first record but at the same time I require the date from column position 4 to 11 on the header to be used as a transaction date when loading the other records
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
