Variables in SQL Loader

[eeriehunk]

Hi All,
Can anyone kindly give me an approach to use a variable in a sql loader ctl file. I am trying to add the value before each insert of row and this value is the file name. So the question is how can I dynamically identify the input data file name, if not, is there a way I can make my SQL Loader to insert a value (file name) before each row into the table.
Regards,
Aj

[Saii]

Can you please elaborate some more -is this column not available in data file, at what position you want to insert,where to insert this row as you said "insert a value (file name) before each row into the table."

[eeriehunk]

Hi Saii, Thanks for your reply. What I am trying to accomplish here is, I want to insert the data file name along with other data in the data file into table X and this table has the file name coloumn, but the data file does not contain the file name as one of its contents.
Regards, Aj

[Saii]

You want to load the data file name into the table so I am guessing that for all the rows in that file, the name will be same. just from top of my head,i guess you can load this column as blank and issue an update statement just after loading the data in your script(assuming you are doing this in script).
If this is not helping can you provide a sample of the control file and explain your current approach of implementing this process.

[brolon]

I am facing the exact same problem ... I must load several files sequentially in a loop. The input files contain 2 columns. The target table contains 3 columns. For all the records coming from the same file, the third column must be filled in with an ID.

By example :

File 1 contains :

col_1_1,col_2_1
col_1_2,col_2_2

File 2 contains :

col_1_1_B,col_2 _1_B
col_1_2_B,col_2 _2_B
col_1_3_B,col_2 _3_B

In my target table, the result must be :
COL1 COL2 COL3
--------------------------------------------------------------
1 col_1_1 col_2_1
1 col_1_2 col_2_2
2 col_1_1_B col_2_1_B
2 col_1_2_B col_2_2_B
2 col_1_3_B col_2_3_B

I've tried to use something like that in my CTL file:

(
CPT %ID%,
COL1 CHAR,
COL2 CHAR
)

But without success. I tried to quote %ID% but it failed too. It does not seem to be possible to use DOS variable in that way. Is that right?

So what ?
- In my case, generating the CTL file dynamically is not recommandable.
- I can create temporary input files where 1 add the %ID% variable in front of each line. But that is not a good idea, if I have to treat a huge quantity of big files.
- I can insert a file, leave the %ID% column blank. Set its value using sqlplus if null). Then I process the second file, ... and so on.

Are there any other options?

Thank you.

Kind regards