Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Timestamp and external table

Greg SpallMay 29 2013 — edited May 29 2013

I have a simple, 1 line datafile:

2009-01-14|2009-01-15:00:00:00.000000

CREATE TABLE junk_ext
   (
      some_date                     DATE,
      some_timestamp                TIMESTAMP(6)
   )
  ORGANIZATION EXTERNAL
   (  TYPE ORACLE_LOADER
      DEFAULT DIRECTORY GREG1
      ACCESS PARAMETERS
      (  RECORDS DELIMITED BY NEWLINE
         CHARACTERSET WE8ISO8859P1
         FIELDS TERMINATED BY '|'
         MISSING FIELD VALUES ARE NULL
        (
            some_date                DATE   "yyyy-mm-dd",
            some_timestamp           DATE   "yyyy-mm-dd:hh:mi:ss.ff"
         )
      )
      LOCATION
      ( 'test1.txt' )
   );

SELECT * FROM junk_ext;

And when the SELECT runs, it crashes out with "end of file on communication channel O_0

Table dropped.


Table created.

SELECT * FROM junk_ext
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 20681
Session ID: 161 Serial number: 25492

Trace file:

Trace file xxxxx/xxxxx_ora_20681.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = xxxxx
System name:    Linux
Node name:      xxxxx
Release:        2.6.18-128.el5
Version:        #1 SMP Wed Jan 21 08:45:05 EST 2009
Machine:        xxxxx
Instance name: xxxxx
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 20681, image: xxxxx (TNS V1-V3)


*** 2013-05-29 15:29:41.369
*** SESSION ID:(161.25492) 2013-05-29 15:29:41.369
*** CLIENT ID:() 2013-05-29 15:29:41.369
*** SERVICE NAME:(SYS$USERS) 2013-05-29 15:29:41.369
*** MODULE NAME:(SQL*Plus) 2013-05-29 15:29:41.369
*** ACTION NAME:() 2013-05-29 15:29:41.369
 
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0x1EAB9DB, ldxsnfcom()+601] [flags: 0x0, count: 1]
DDE: Problem Key 'ORA 7445 [ldxsnfcom()+601]' was flood controlled (0x2) (incident: 20513)
ORA-07445: exception encountered: core dump [ldxsnfcom()+601] [SIGSEGV] [ADDR:0x8] [PC:0x1EAB9DB] [Address not mapped to object] []
KUP-03018: invalid date mask "" specified for column 
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL

without the timestamp column it works just fine (ie just the date).
I'm sure I'm mis-handling TIMESTAMPS - but I can't find anything in the docs that helps out.

I tried :

            some_timestamp           TIMESTAMP(6)

instead of:

            some_timestamp           DATE   "yyyy-mm-dd:hh:mi:ss.ff"

as per the documentation, but it definitely doesn't recognize that TIMESTAMP keyword at that time.

Any help would be appreciated!