Solved

Oracle Sql Loader / PLSQL Data Validation

Posted on 2014-03-26
6
1,527 Views
Last Modified: 2014-03-26
I receive a pipe delimited file that I load using SQL Loader. I have been asked to verify the pipe count in the file as a means to validate that all columns are accounted for.

Example: The file should be
col1|col2|col3|col4|col5

If I get a file like this
col1|col2|col3
it should not accept the file.

The pipe count should be five not two for each row.

My questions is two fold:

1. Will SQL Loader bomb on a file that does not have all of the columns defined, I'm receiving a variable length file.

2. Is there a way to do this with a stored procedure and can you give me ideas? I'm thinking of using UTL_File or should I load file in one big column and count the | in each row. Usually these files are large and I'm not sure about an approach.

Thanks.
0
Comment
Question by:SharonBernal
[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
6 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39956285
On what kind of file system do these files reside (e.g. Windows, Unix, etc.)?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39956310
on a windows server use this scripts
and providing the records are 1 line based

findstr /V /N ".*|.*|.*|.*|.*" c:\data\your_input_file.txt > incorrect_lines.txt

Open in new window


it should give a list of the incorrect lines preceded by a line number
doing a line count on the file gives the count
0
 

Author Comment

by:SharonBernal
ID: 39956317
It's a Unix system.
0
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!

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39956329
don't have unix but i'm guessing grep can do that
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 39956652
What behavior are you looking for?  Should SQL*Loader ignore the records that are missing columns, or should the whole file be ignored?

If you are looking to just ignore the records that are missing columns, then as long as you do not specify TRAILING NULLCOLS in the control file, you should be OK.  See the documentation here -> http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_control_file.htm#SUTIL1121

If you want to ignore the whole file, then you be sure that you do not set TRAILING NULLCOLS and make sure that you also set ERRORS=0.  That should cause the load to terminate on the first error.
0
 

Author Closing Comment

by:SharonBernal
ID: 39956743
Or business rule would be to ignore the entire file. This is what I needed to know. Thank you.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database Design Dilemma 6 71
Loading flat file data in tables 2 99
Dbms_job.change procedure 16 36
Force XMLSEQUENCE to return empty tags for null values. 10 45
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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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