Home » RDBMS Server » Server Utilities » How to load tabbed data lines for specific header (SQL*LOADER,10g, R12)
How to load tabbed data lines for specific header [message #469650] Thu, 05 August 2010 06:42 Go to next message
mastu
Messages: 3
Registered: August 2010
Location: Bangalore, India
Junior Member
Hi,

I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.

Any idea?

Mastu

eg of flat file.
Header
trans_code  comp                 date           rec_type	
------------------------------------------------------------
8	    12800002		 07292010	05
transcode_line acct             date           refrence
------------------------------------------------------------
4	4246040010027383	07272010 24427330207710017569675
4	4246040010027383	07272010 24427330207710017569675

[Updated on: Thu, 05 August 2010 06:45] by Moderator

Report message to a moderator

Re: How to load tabbed data lines for specific header [message #469699 is a reply to message #469650] Thu, 05 August 2010 10:01 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Well this is the first time I have come across such requirement... I believe it can not be done at loader level

Which OS you are using ? If you are on unix/linux you might want to USE shell Script to perform the separation task of header and then load the rest of the file in the database.
Re: How to load tabbed data lines for specific header [message #469700 is a reply to message #469650] Thu, 05 August 2010 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
>Any idea?
use EXTERNAL TABLE & custom PL/SQL to load data as required
Re: How to load tabbed data lines for specific header [message #469701 is a reply to message #469700] Thu, 05 August 2010 10:08 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan wrote on Thu, 05 August 2010 10:03
>Any idea?
use EXTERNAL TABLE & custom PL/SQL to load data as required


Yes this can be a alternative to Shell script .. but one thing is for sure OP will have to separate the header first before load the file in the database.

[Edit: Typo ]

[Updated on: Thu, 05 August 2010 10:09]

Report message to a moderator

Re: How to load tabbed data lines for specific header [message #469704 is a reply to message #469701] Thu, 05 August 2010 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
> but one thing is for sure OP will have to separate the header first before load the file in the database.
or not.
"map" EXTERNAL FILE to records of single column of VARCHAR2(4000) & parse as needed
Re: How to load tabbed data lines for specific header [message #469705 is a reply to message #469704] Thu, 05 August 2010 10:20 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan wrote on Thu, 05 August 2010 10:18
> but one thing is for sure OP will have to separate the header first before load the file in the database.
or not.
"map" EXTERNAL FILE to records of single column of VARCHAR2(4000) & parse as needed


Not Sure What it Means BlackSwan.
Re: How to load tabbed data lines for specific header [message #469706 is a reply to message #469705] Thu, 05 August 2010 10:26 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
My Suggestion was based on the file structure OP has provided.
Re: How to load tabbed data lines for specific header [message #469799 is a reply to message #469650] Thu, 05 August 2010 17:30 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
You can use a when clause:

when rec_type = '05'

to tell it which ones to load.

The rest depends on your data and what you want loaded. I would need to see some more data, some info about the data such as whether the number of lines for each section is the same, and the structure of the target table and a sample of what results you want to see in the target table, based on the sample data. If the number of rows are consistent, then you can use that. If not, then you can separate the records by a delimiter such as the word "header" if it is actually there in the data and treat the detail rows as arrays if they need to be loaded. These are just a few ideas. There are always multiple ways to do things.

Previous Topic: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g.
Next Topic: Imp-00033 table not found while importing
Goto Forum:
  


Current Time: Thu Sep 18 07:10:05 CDT 2014

Total time taken to generate the page: 0.09155 seconds