Wednesday, October 1, 2008

Oracle External Table error for Subquery

Oracle 10g 10.2.0.4 and below (BUG 5600425 at metalink)

Oracle says that the bug is fixed in 10.2.0.4 but it is not.

So, if you are trying to access an external table using a subquery and a NOT IN clause, the query may error out due to field formatting errors. Even if you use SKIP 1 for the external table definition and your external file has a header row, the following query may return a SQL Loader error.

      DELETE FROM cfgview
        WHERE cfgviewid NOT IN (SELECT view_id
                                FROM vw_ext_acc_configview);

An alternative is to use

      DELETE FROM cfgview cv
        WHERE NOT EXISTS (SELECT NULL
                 FROM vw_ext_acc_configview vecv
                WHERE vecv.view_id = CV.cfgviewid);