create a bad file while creating an external table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: create a bad file while creating an external table

Hybrid View

  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Cool create a bad file while creating an external table

    hello,
    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?

    ganit.

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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;

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    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.

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Quote Originally Posted by gganit
    The count(*) method does not work, just as 'select fieldname from ext_table' doesn't do the trick.
    I can't test this right now. What doesn't work about COUNT(*) FROM external_table?

    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 12:56 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width