I'm having a problem with External tables. Most of the time I can read the data in the table without any issue. But when I issue the query below I get an error.
Contents of test.csv fileTool type,ID
Common,1
Type1,2
Type2,3
CREATE TABLE "TEST_IMPORT"
( "MACHINE" VARCHAR2(20 BYTE),
"ID" NUMBER
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
( records delimited by newline skip 1
fields terminated by ','
optionally enclosed by '"' )
LOCATION
( "IMPORT_DIR":'test.csv'
)
)
;
SQL> select count(*) from test_import;
COUNT(*)
----------------------
3
1 rows selected
SQL> create table perm_tbl as select * from test_import where upper(machine) in
('COMMON','TYPE1');
Table created.
SQL> desc perm_tbl;
Name Null? Type
----------------------------------------- -------- ----------------------------
MACHINE VARCHAR2(20)
ID NUMBER
select * from (
select id from perm_tbl where upper(machine) in ('COMMON','TYPE1')
);
ID
----------------------
1
2
2 rows selected
SQL> select * from perm_tbl where id not in
2 (select id from test_import where upper(machine) in ('COMMON','TYPE2'));
select * from perm_tbl where id not in
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
Log file:
LOG file opened at 04/29/08 13:08:03
Field Definitions for table TEST_IMPORT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
MACHINE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
error processing column ID in row 1 for datafile C:\test.csv
ORA-01722: invalid number
BAD file:
Tool type,ID
you are assuming an order of operation in SQL processing and you cannot, we can evaluate anything in any order we want.
SQL> create table perm_tbl as select * from test_import where upper(machine) in
('COMMON','TYPE1');
we apparently evaluated "where uppper() in (...)" before selecting machine and id - and hence converting the string into a number for ID - and therefore did NOT fail.
for:
SQL> select * from perm_tbl where id not in
2 (select id from test_import where upper(machine) in ('COMMON','TYPE2'));
well, one way to process that query is:
select p.*
from perm_tbl p, test_import t
where p.id = t.id(+)
and t.machine in ('COMMON','TYPE2')
and t.id is null;
(an anti-join), there are other ways as well - but as you can see, the SQL you "get" is not necessary the sql you wrote - they are equivalent, but different.
It is all about the plan... And the accidental order of operation.
Here is a potential plan for your query:
ops$tkyte%ORA10GR2> select * from perm_tbl where id not in
2 (select id from test_import where upper(machine) in ('COMMON','TYPE2'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | PERM_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 3 | EXTERNAL TABLE ACCESS FULL| TEST_IMPORT | 2 | 50 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST_IMPORT" "TEST_IMPORT" WHERE
(UPPER("MACHINE")='COMMON' OR UPPER("MACHINE")='TYPE2') AND LNNVL("ID"<>:B1)))
3 - filter((UPPER("MACHINE")='COMMON' OR UPPER("MACHINE")='TYPE2') AND
LNNVL("ID"<>:B1))
Note
-----
- dynamic sampling used for this statement
see the filter on step 3? that is why...
if you know that when machine in (values) means ID is a number, you would:
case when upper(machine) in ('A','B') then to_number(id) end
and map ID as a STRING - which is apparently is...