Solved

Filter data in Sql Ldr

Posted on 2013-05-30
3
366 Views
Last Modified: 2013-06-11
I have the following Sql Ldr file. I want to insert only the data - excluding the header or trailer or in other words anything after start-of-data and before end-of-data
I know that the third field and fifth is numeric, and my data is pipe delimited. The first column can be any characters in length.

START-OF-FILE
START-OF-HEADER
EXECUTION-DATE=2010-05-29-12.17.50.007000
FILE-DATE=2010-05-29
END-OF-HEADER
START-OF-DATA
102|26340|5|ABC|0
100|48585|2|DEF|0
100|48571|2|GHI|1
END-OF-DATA
START-OF-TRAILER
EXECUTION-DATE=2013-05-29-16.17.50.007000
FILE-DATE=2013-05-29
END-OF-TRAILER
END-OF-FILE

In the WHEN clause can i filter data to check for a pipe sign, or numeric/char values in data columns?
0
Comment
Question by:gram77
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39207583
As long as you don't mind errors in your log file, just load it.

If you have 5 column declared in the control file that are pipe delimited, then only 3 rows in that file match so only 3 rows will be loaded.

I'll assume this won't be good enough so I'll see if I can find some magic way to figure out how to only load 'good' rows without errors in the log file.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39207709
Sorry but I've not been able to come up with any magic tricks to pre-check the record before it is processed.

Maybe some other Experts know of a way.

You can SKIP a specific number of rows.

Based on the sample data the last column looks like it might be either a 1 or 0.  IF this is the case then you might be able to use WHEN to check that but I still get format errors on the rest of the rows:

Record 1: Rejected - Error on table TAB1, column COL2.
Column not found before end of logical record (use TRAILING NULLCOLS)

When you add trailing nulcols, you get different errors in the log file:
Record 1: Rejected - Error on table TAB1, column COL1.
ORA-01722: invalid number

Not sure how to do this without errors in the log file.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39207887
What about pre-processing the file to only grab the lines you want?  This should do it:

sed -e '1,/^START-OF-DATA/d' -e '/^END-OF-DATA/,$d' yourfile > newfile

Then just use sqlldr to load the new file.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now