ronythom
asked on
Data loading using sqlldr in Oracle
I have a file filename.txt with header record and detail records as follows
first row is always header record
filename.txt|20110509
T|name|20110504|100|
T|name1|20110504|100|
I would like to load this data into 2 different table
1. Tmp_header_table
(name, date)
2.Tmp_detail_table
(type,date,amount,filler)
Would you please help me to write the ctl file for this problem
first row is always header record
filename.txt|20110509
T|name|20110504|100|
T|name1|20110504|100|
I would like to load this data into 2 different table
1. Tmp_header_table
(name, date)
2.Tmp_detail_table
(type,date,amount,filler)
Would you please help me to write the ctl file for this problem
ASKER
slightwv, The "type" is always one character and file name not.
Also there is only one header record.
Mohammad, I cannot add the data inthe control file becuase the file comes with 1000's of records and it is not one time approach it is going to be every month as scheduled.
SO I need the general approach with input file
like
load data
INFILE 'mydata.txt'
into table TMP_HEADER_table
FIELDS TERMINATED by "|" trailing NULLCOLS
(name, date)
into table tmp_detail_table FIELDS TERMINATED by "|" trailing NULLCOLS
(type,date,amount,filler)
So what should I add in this ctl for seperating header record and detail records
Also there is only one header record.
Mohammad, I cannot add the data inthe control file becuase the file comes with 1000's of records and it is not one time approach it is going to be every month as scheduled.
SO I need the general approach with input file
like
load data
INFILE 'mydata.txt'
into table TMP_HEADER_table
FIELDS TERMINATED by "|" trailing NULLCOLS
(name, date)
into table tmp_detail_table FIELDS TERMINATED by "|" trailing NULLCOLS
(type,date,amount,filler)
So what should I add in this ctl for seperating header record and detail records
Only one header row doesn't help if you want to do this with a single controlfile. The only way to load multiple tables from one controlfile and one call to sqlldr is to use WHEN.
You can easily load this using two separate calls to sqlldr and two control files.
First has options: LOAD=1.
Second uses the option: SKIP=1.
I'll work on examples using your test data.
You can easily load this using two separate calls to sqlldr and two control files.
First has options: LOAD=1.
Second uses the option: SKIP=1.
I'll work on examples using your test data.
Does it absolutely have to be SQL*Loader?
If you did this with an external table and a PL/SQL block, you could do it all in one pass.
If you did this with an external table and a PL/SQL block, you could do it all in one pass.
ASKER
Johnsone Do you have the sample code for PL/SQL block
If the file to be loaded is on the database server (or file system mounted by the database server) then external tables are likely the best option here.
Confirm this and I'll stop on a sql loader control file and work on an external table example. Besides, I'm not having much luck with a single controlfile for sql loader anyway.
Confirm this and I'll stop on a sql loader control file and work on an external table example. Besides, I'm not having much luck with a single controlfile for sql loader anyway.
ASKER
How the external table works....?
I would like to have the solution doesn't matter the approch.
I would like to run this job every month in a fixed time. By that time I do have the data file in my server. So I need to run this job toload the data to the header and detail table.
If we can do it by using external tables then I am happy to do that.
Please help me on it
I would like to have the solution doesn't matter the approch.
I would like to run this job every month in a fixed time. By that time I do have the data file in my server. So I need to run this job toload the data to the header and detail table.
If we can do it by using external tables then I am happy to do that.
Please help me on it
External tables are created as physical objects in the database. They look just like a real table. The difference is they use the same syntax as sql loader and the source of the data is actually on the file system.
The drawback is the source file must be accessible from the physical database server.
If the source file is remote, then sqlloader can load it or you would need additional code to get it to the database server.
I'll defer you to the docs for the details:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896
The drawback is the source file must be accessible from the physical database server.
If the source file is remote, then sqlloader can load it or you would need additional code to get it to the database server.
I'll defer you to the docs for the details:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896
Quick question:
You have a table: Tmp_detail_table(type,date ,amount,fi ller)
The data is: T|name1|20110504|100|
What goes where? I assumed 'T' was the 'type'. If so, what are the allowed values?
Below is the working sample using two control files. I had to guess at the detail table until you answer the question above.
You have a table: Tmp_detail_table(type,date
The data is: T|name1|20110504|100|
What goes where? I assumed 'T' was the 'type'. If so, what are the allowed values?
Below is the working sample using two control files. I had to guess at the detail table until you answer the question above.
options (LOAD=1)
load data
infile 'c:\q.txt'
truncate INTO TABLE tmp_header_table
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
name,
mydate DATE "yyyymmdd"
)
detail controlfile:
options (SKIP=1)
load data
infile 'c:\q.txt'
truncate INTO TABLE tmp_detail_table
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
mytype,
somecolumn,
mydate DATE "yyyymmdd",
amount,
filler
)
--test table
drop table tmp_header_table purge;
create table Tmp_header_table (name varchar2(20), mydate date);
drop table tmp_detail_table purge;
create table Tmp_detail_table(mytype char(1), somecolumn varchar2(10), mydate date,amount number,filler char(1));
ASKER
These temp tables created each time before loading the data from file. field names in brackets.
1. Tmp_header_table
(name, date)
2.Tmp_detail_table
(type,name,date,amount,fil ler)
My plan is
1.load the header data to Tmp_header_table
2. load the detail records to Tmp_header_table
3. These tmp tables i am using as stage table because I do have lot of validations before I append to the orginal table.
Can I call 2ctl files from same shell?
1. Tmp_header_table
(name, date)
2.Tmp_detail_table
(type,name,date,amount,fil
My plan is
1.load the header data to Tmp_header_table
2. load the detail records to Tmp_header_table
3. These tmp tables i am using as stage table because I do have lot of validations before I append to the orginal table.
Can I call 2ctl files from same shell?
>>1.load the header data to Tmp_header_table
>>2. load the detail records to Tmp_header_table
>>3. These tmp tables i am using as stage table because I do have lot of validations before I append to the orginal table.
Using external tables will remove the TMP table design.
You've never stated if the file to be loaded is on the database server.
>>Can I call 2ctl files from same shell?
It's a shell script, you can call sqlldr as many times as you want.
>>2. load the detail records to Tmp_header_table
>>3. These tmp tables i am using as stage table because I do have lot of validations before I append to the orginal table.
Using external tables will remove the TMP table design.
You've never stated if the file to be loaded is on the database server.
>>Can I call 2ctl files from same shell?
It's a shell script, you can call sqlldr as many times as you want.
ASKER
---You've never stated if the file to be loaded is on the database server.
I have checked with my DBA ,as per our current system I need to follow the existing policy to load the data. So let me go for 2 ctl files and work on it. Because the approved design is load the data to tmp table and work from there.
I have checked with my DBA ,as per our current system I need to follow the existing policy to load the data. So let me go for 2 ctl files and work on it. Because the approved design is load the data to tmp table and work from there.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'll try to get a single controlfile and one call to sqlldr but need to know about that type column.
Question from above:
You have a table: Tmp_detail_table(type,date ,amount,fi ller)
The data is: T|name1|20110504|100|
What goes where? I assumed 'T' was the 'type'. If so, what are the allowed values?
Question from above:
You have a table: Tmp_detail_table(type,date
The data is: T|name1|20110504|100|
What goes where? I assumed 'T' was the 'type'. If so, what are the allowed values?
ASKER
the column "type" is always size 1. itholds only character values like "T", "A" etc
>>itholds only character values like "T", "A" etc
If there was a simple small list then a single file might make sense. The bad part is there is no 'OR' in a controlfile (or I cannot find one). You can add a duplicate WHEN clause for each allowable entry.
Here's the single controlfile for 'T' and 'A' only. I'm not sure about the pipe delimiter as the 'second character' but on a quick test it appears to work.
If there was a simple small list then a single file might make sense. The bad part is there is no 'OR' in a controlfile (or I cannot find one). You can add a duplicate WHEN clause for each allowable entry.
Here's the single controlfile for 'T' and 'A' only. I'm not sure about the pipe delimiter as the 'second character' but on a quick test it appears to work.
load data
infile 'c:\q.txt'
truncate INTO TABLE tmp_detail_table when mytype='A'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
mytype,
somecolumn,
mydate DATE "yyyymmdd",
amount,
filler
)
INTO TABLE tmp_detail_table when mytype='T'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
mytype position(1),
somecolumn,
mydate DATE "yyyymmdd",
amount,
filler
)
INTO TABLE tmp_header_table when (01:02) != 'A|' and (01:02) != 'T|'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
name position(1),
mydate DATE "yyyymmdd"
)
- we can do this in 1 control file. i've tested this.
- in the control file we need to specify the 1st column in the 1st row = filename.txt as a header (name = 'filename.txt') so that it will be inserted into tmp_header_table, and not to process it during the load to Tmp_detail_table by specifying the first column in the 2nd row (filler <> 'filename.txt')
- as for the date column, you might need to format it based on your date format.
- as pointed by slightwv, your Tmp_detail_table (type,date,amount,filler) doest match with the row in the DAT file: T|name1|20110504|100|. so i guess T=filler, name=type, 20110504=date and 100=amount. so adjust the following code if my assumption is wrong:
- in the control file we need to specify the 1st column in the 1st row = filename.txt as a header (name = 'filename.txt') so that it will be inserted into tmp_header_table, and not to process it during the load to Tmp_detail_table by specifying the first column in the 2nd row (filler <> 'filename.txt')
- as for the date column, you might need to format it based on your date format.
- as pointed by slightwv, your Tmp_detail_table (type,date,amount,filler) doest match with the row in the DAT file: T|name1|20110504|100|. so i guess T=filler, name=type, 20110504=date and 100=amount. so adjust the following code if my assumption is wrong:
LOAD DATA
INSERT INTO TABLE Tmp_header_table
WHEN name = 'filename.txt'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
name,
date
)
INTO TABLE Tmp_detail_table
WHEN filler <> 'filename.txt'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
filler POSITION(1),
type
date,
amount,
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Slightwv,
Can we check the second position for |...like when (02) = '|' in the ctl file.. instead of checking like when(01:02)
I tried but I got error.
I cannot promise you the first character is always T or A also I am not sure what else character should be coming in. but I can gaurantee that the second character in detail record is always '|'
OP Zaharin :: detail header file contains the fields
(Type,name, date,amount,filler)
Also the filename.txt is not fixed, it may change eachtime so I cannot hard code like that.
One thing i can promise is the detailed records the second characteris always '|', if we can create the ctl filebased on this then it will be appreciated.
Can we check the second position for |...like when (02) = '|' in the ctl file.. instead of checking like when(01:02)
I tried but I got error.
I cannot promise you the first character is always T or A also I am not sure what else character should be coming in. but I can gaurantee that the second character in detail record is always '|'
OP Zaharin :: detail header file contains the fields
(Type,name, date,amount,filler)
Also the filename.txt is not fixed, it may change eachtime so I cannot hard code like that.
One thing i can promise is the detailed records the second characteris always '|', if we can create the ctl filebased on this then it will be appreciated.
>>instead of checking like when(01:02)
I wasn't even sure that was working the way I thought it was since the controlfile sets the delimiter to a '|'. I actually expected the field to be parsed first.
I'm thinking it was just dumb luck it worked.
What you are running into is sql loader is very good at doing what it was designed to do: load records into Oracle very efficiently.
It was never designed to have a lot of logic or control over that data.
I'll see if I can think of another 'trick' but to be honest, I think I've tapped out my bag-of-tricks already.
I would count on two calls and two control files.
I wasn't even sure that was working the way I thought it was since the controlfile sets the delimiter to a '|'. I actually expected the field to be parsed first.
I'm thinking it was just dumb luck it worked.
What you are running into is sql loader is very good at doing what it was designed to do: load records into Oracle very efficiently.
It was never designed to have a lot of logic or control over that data.
I'll see if I can think of another 'trick' but to be honest, I think I've tapped out my bag-of-tricks already.
I would count on two calls and two control files.
- you need to change the filler columnname in your table to other name. filler is a keyword used by sqlloader. it will create error when loading to that column using sql loader.
- can u tell us how do you map T|name1|20110504|100| to (type,date,amount,filler)? which column in the data file goes to which column in the table?
"Also the filename.txt is not fixed, it may change eachtime so I cannot hard code like that."
- yes you will always need to change the ctl file whenever a new DAT file is to be process to match the content of the DAT file.
"One thing i can promise is the detailed records the second characteris always '|', if we can create the ctl filebased on this then it will be appreciated."
- FIELDS TERMINATED BY "|" specified that. we use it only as the column delimiter.
- my method in the previous posting works, if you willing to change the ctl file to meet changes everytime a new DAT file to be process. as slightwv said "It was never designed to have a lot of logic or control over that data".
- can u tell us how do you map T|name1|20110504|100| to (type,date,amount,filler)?
"Also the filename.txt is not fixed, it may change eachtime so I cannot hard code like that."
- yes you will always need to change the ctl file whenever a new DAT file is to be process to match the content of the DAT file.
"One thing i can promise is the detailed records the second characteris always '|', if we can create the ctl filebased on this then it will be appreciated."
- FIELDS TERMINATED BY "|" specified that. we use it only as the column delimiter.
- my method in the previous posting works, if you willing to change the ctl file to meet changes everytime a new DAT file to be process. as slightwv said "It was never designed to have a lot of logic or control over that data".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys...
For example, is the 'type' column ALWAYS one character? Can the filename NEVER be just one character?
Once I get an answer to that question, I'll provide a working model.