Oracle Sql Loader / PLSQL Data Validation
Posted on 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
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.