Solved

Filter data in Sql Ldr

Posted on 2013-05-30
3
370 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

777 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