create a bad file while creating an external table
I need to create an external table and after the creation i have to check if a bad file was created.
do i have to execute "SELECT * FROM EXT_TBL" to make sure all the bad recors were found?
Is there a way to make the table create the bad file at the "CREATE TABLE" or "ALTER TABLE ... LOCATION .." command?
The badfile contains the rows that failed parsing, and for that to happen it has to have attempted to read the record.
Perhaps you could
SELECT COUNT(*) FROM external_table;
We've been looking for exactly the same thing here, but to no avail.
The count(*) method does not work, just as 'select fieldname from ext_table' doesn't do the trick.
I still have a problem coming to terms with the fact that I have to iterate over all the records in the table myself, manually, just to find bad records, but it looks like this will have to be the solution.
I can't test this right now. What doesn't work about COUNT(*) FROM external_table?
Originally Posted by gganit
I can imagine that 'select fieldname from ext_table' doesn't give the desired results because it will avoid parsing beyond the specified column, and can therefore potentially miss parse errors elsewhere in the record. Possibly it would be worth checking the PROJECT COLUMN REFERENCED|ALL setting for the table (10g onwards), in case it is currently REFERENCED which limits parsing to columns actually referenced in the SELECT list of a query. The default is ALL however, so this may not be a factor.
No, there is no set of validation commands for external tables. If you need it to parse the data you have to tell it to read the data, which is what any validation command would have to do anyway if there were one. Perhaps CREATE TABLE x NOLOGGING AS SELECT * FROM external_table would be another option.
Last edited by WilliamR; 06-11-2006 at 01:56 PM.
Click Here to Expand Forum to Full Width