Database Utilities (MOSC)

MOSC Banner

Loading ORACLE_DATE type doesn't work if external table data is "0x00000000000000".

edited Jul 23, 2010 3:08AM in Database Utilities (MOSC) 7 commentsAnswered
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,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center