Solved

Filter data in Sql Ldr

Posted on 2013-05-30
3
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

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 77

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 35

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

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!

Question has a verified solution.

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

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

739 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