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);
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);
No comments:
Post a Comment