Loading ORACLE_DATE type doesn't work if external table data is "0x00000000000000".
Hello,
I've an external table which has a type of ORACLE_DATE. The datafile inclues 7-byte "0x00" for NULL values. But, if I select from this column, it doesn't return NULL, it returns wrong date.
NULLIF <columnName>= "0x00" or "0x00000000000000"did not help.
Here is the ddl:
CREATE TABLE dunal.type_test_ext
(
"F1" NUMBER,
"F2" VARCHAR2(10),
"F3" CHAR(20),
"F4" DATE,
"F5" RAW(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS
(
RECORDS VARIABLE 2
DATA IS LITTLE ENDIAN
CHARACTERSET WE8MSWIN1252
LANGUAGE AMERICAN
TERRITORY AMERICA
BYTEORDERMARK NOCHECK
STRING SIZES ARE IN BYTES
FIELDS
(
"F1" ORACLE_NUMBER COUNTED,
I've an external table which has a type of ORACLE_DATE. The datafile inclues 7-byte "0x00" for NULL values. But, if I select from this column, it doesn't return NULL, it returns wrong date.
NULLIF <columnName>= "0x00" or "0x00000000000000"did not help.
Here is the ddl:
CREATE TABLE dunal.type_test_ext
(
"F1" NUMBER,
"F2" VARCHAR2(10),
"F3" CHAR(20),
"F4" DATE,
"F5" RAW(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS
(
RECORDS VARIABLE 2
DATA IS LITTLE ENDIAN
CHARACTERSET WE8MSWIN1252
LANGUAGE AMERICAN
TERRITORY AMERICA
BYTEORDERMARK NOCHECK
STRING SIZES ARE IN BYTES
FIELDS
(
"F1" ORACLE_NUMBER COUNTED,
0