471,354 Members | 1,684 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Variables in SQL Loader

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
Apr 7 '09 #1
4 6820
Saii
145 Expert 100+
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."
Apr 15 '09 #2
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
Apr 15 '09 #3
Saii
145 Expert 100+
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.
Apr 17 '09 #4
brolon
1
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
Apr 9 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Naresh Agarwal | last post: by
2 posts views Thread by Bo Brunsgaard | last post: by
115 posts views Thread by Mark Shelor | last post: by
111 posts views Thread by Nate | last post: by
25 posts views Thread by sidd | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.