Oracle Sql Loader / PLSQL Data Validation

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

If I get a file like this
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.

Who is Participating?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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 ->

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.
On what kind of file system do these files reside (e.g. Windows, Unix, etc.)?
Geert GOracle dbaCommented:
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
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

SharonBernalAuthor Commented:
It's a Unix system.
Geert GOracle dbaCommented:
don't have unix but i'm guessing grep can do that
SharonBernalAuthor Commented:
Or business rule would be to ignore the entire file. This is what I needed to know. Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.