473,241 Members | 1,733 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 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 6954
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

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

Similar topics

1
by: Naresh Agarwal | last post by:
Hi I've written a Custom class loader, which reads the bytecode from a database and returns the Class object. For example, bytecode of a class "Test" is stored in the database. Through custom...
2
by: Bo Brunsgaard | last post by:
I have a problem trying to run SQL*Loader after migrating to Oracle 9. It used to run with no hicups on Oracle 8i, and I'm at my wits' end. Any help? I'm trying to run SQL*Loader from a command...
6
by: Hari Om | last post by:
Here are the details of my error log files: I execute the command and get following message at console: ---------------------------------------------------------------------- ../sqlldr...
115
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
27
by: Madhav | last post by:
Hi all, I did not understand why do the global vars are initialized to NULL where as the block level variables have random values? I know that the C standard requires this as was mentioned in a...
4
by: DaTurk | last post by:
Hi, I've noticed that when trying to create a managed, auto_gcrooted variable in a antive class, the compiler throws linker exceptions. While searching some forums I found out that this is a...
111
by: Nate | last post by:
Hello, I am looking for a method to automatically declare variables in C. I'm not sure if there is a good way to do this, but I had something like this in mind... int i; for(i = 1; i < 4;...
5
by: vshalpatel | last post by:
Hi I want to use SQL*Loader , an Oracle-supplied utility to load data from a flat file into one database tables. for this I have write the scripts in the SQL*LOADER control file named ...
25
by: sidd | last post by:
In the following code: int i = 5; ---it goes to .data segment int j; ---it goes to bss segment int main() { int c; int i = 5; ---stack
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.