?
Solved

Filter data in Sql Ldr

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

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 78

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

839 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