?
Solved

Filter data in Sql Ldr

Posted on 2013-05-30
3
Medium Priority
?
396 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 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