[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

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

0
ronythom
Asked:
ronythom
  • 10
  • 8
  • 3
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
If you can set some sort of delimiter between the rows then you can do this with a single control file.

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.
0
 
ronythomAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johnsoneSenior Oracle DBACommented:
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.
0
 
ronythomAuthor Commented:
Johnsone  Do you have the sample code for PL/SQL block
0
 
slightwv (䄆 Netminder) Commented:
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.


0
 
ronythomAuthor Commented:
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

0
 
slightwv (䄆 Netminder) Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
Quick question:
You have a table:  Tmp_detail_table(type,date,amount,filler)

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"
   )

Open in new window

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
   )

Open in new window

--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));

Open in new window

0
 
ronythomAuthor Commented:
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,filler)

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?
0
 
slightwv (䄆 Netminder) Commented:
>>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.

0
 
ronythomAuthor Commented:
---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.



0
 
johnsoneSenior Oracle DBACommented:
I think you are well underway with the examples of how to create the external tables.  As slightwv has mentioned, the files must be on the database server for that solution to work.

As far as a PL/SQL block to move the data that it all depends on what you need to do with the data.  Based on your original descriptions, it should simply be 2 inserts, but you can write a store procedure (or anonymous block) to do anything you need.

Assuming that you are creating the external table as TMP_DATA_TABLE, moving the data should simply be:

insert into ?? select name, date from tmp_header_table;
insert into ?? select type, name, date, amount, filler from tmp_data_table;

If you already have a procedure that is going to do work on your original temporary tables then just change the table name in the procedures to the name of your external table.
0
 
slightwv (䄆 Netminder) Commented:
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,filler)

The data is:  T|name1|20110504|100|

What goes where?  I assumed 'T' was the 'type'.  If so, what are the allowed values?
0
 
ronythomAuthor Commented:
the column "type" is always size 1. itholds only character values like "T", "A" etc
0
 
slightwv (䄆 Netminder) Commented:
>>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.
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"
)

Open in new window

0
 
OP_ZaharinCommented:
- 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:

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,
)

Open in new window


0
 
OP_ZaharinCommented:
correction to the above code: you cannot used 'filler' as the column name in your Tmp_detail_table because its a keyword in sqlloader. so change your columnname to "fillers" or other name, and change the columnname filler in the code to fillers as follows:

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 fillers <> 'filename.txt'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
(
fillers POSITION(1),
type
date,
amount,
)
0
 
ronythomAuthor Commented:
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.

 
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
OP_ZaharinCommented:
- 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".
0
 
slightwv (䄆 Netminder) Commented:
Just got a few minutes to play with this.

The '(02)' code works for me.  Below is my controlfile.

What error did you get?  Is it the 'filler' reserved word OP_Zaharin mentioned?
load data
infile 'c:\q.txt'
truncate
INTO TABLE tmp_detail_table when (02) = '|'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
	mytype,
	somecolumn,
	mydate DATE "yyyymmdd",
	amount,
	filler
)
INTO TABLE tmp_header_table when (02) != '|'
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  	name position(1),
	mydate DATE "yyyymmdd"
)

Open in new window

0
 
ronythomAuthor Commented:
Thank you guys...
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 10
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now