Oracle External Table / DAT file data problem

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 29 Jan 2014 16:33:20 -0600
Message-ID: <CAP79kiSXydsnPiN22aXu8LzwSZZAFNNZGhk1U+ZCWoCvSdY4RA_at_mail.gmail.com>

This is Oracle 9.2. We have external tables defined to load DAT files the vendor sends us.
However, some of the new DAT files have “99999999” or “00000000” in a DATE field inside the DAT file.

I wanted to do a “REPLACE” or something in the external table definition to replace those 9s or 0s with NULL as the table was being read. It doesn’t appear that I can do that.

I’m curious if there is any manipulation I can do on this data without having the vendor send it “correctly”. I may be stuck doing varchar2 definitions and transforming the data on the load from the external table to the application tables but I’m hoping to avoid that and keep it scoped to the external table.

Here’s my attempt at using a replace function. The version with the replace function gives me the error that follows the definition below.

Is there any function or transform I can apply thru Oracle external tables to get this done?

CREATE TABLE RDB_EXT.COMM_ACCTG_HIERARCHY (

  HR_COMMISSION_DATE     DATE,
  HR_RECORD_NUM          NUMBER(7),
  HR_COMM_ACCT_LINE_NUM  NUMBER(3),
  HR_LOG_DATE            DATE,
  HR_RANK                CHAR(2 BYTE),
  HR_AGENT_NUMBER        CHAR(10 BYTE),
  HR_RATE_LEVEL          CHAR(2 BYTE),
  HR_CONT_START_DATE     DATE,
  HR_CONT_END_DATE       DATE

)

ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

     DEFAULT DIRECTORY INSPRO_DATAFILE_DIR
     ACCESS PARAMETERS
       ( records delimited by newline CHARACTERSET WE8MSWIN1252 NOLOGFILE

BADFILE INSPRO_DATAFILE_OUTPUT:'COMM_ACCTG_HIERARCHY.BAD' fields terminated by '|' MISSING FIELD VALUES ARE NULL (

HR_COMMISSION_DATE DATE "mm/dd/yyyy",
HR_RECORD_NUM ,
HR_COMM_ACCT_LINE_NUM ,
HR_LOG_DATE DATE "mm/dd/yyyy",
HR_RANK ,
HR_AGENT_NUMBER ,
HR_RATE_LEVEL ,
HR_CONT_START_DATE DATE "mm/dd/yyyy",
HR_CONT_END_DATE DATE "mm/dd/yyyy"

"replace(:HR_CONT_END_DATE,'00000000','')"
)

 )

     LOCATION (INSPRO_DATAFILE_DIR:'EXT-INSPRO-COMM-HIERARC.DAT')   )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING; select * from rdb_ext.COMM_ACCTG_HIERARCHY *
Error at line 0

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "double-quoted-string": expecting one of:

"comma, defaultif, enclosed, nullif, optionally, ), terminated"

KUP-01007: at line 10 column 37
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

*Chris Taylor | Windsor Health Group | Oracle Database Administrator | (877) 639**-3169 ext. 27231* 7100 Commerce Way, Brentwood, TN 37027

 <http://www.windsorhealthgroup.com/>

• ________________________________ * This message is intended only for the addressee and may contain information that is confidential or privileged. Unauthorized use is strictly prohibited and may be unlawful. If you are not the intended recipient, or the person responsible for delivering to the intended recipient, you should not read, copy, disclose or otherwise use this message, except for the purpose of delivery to the addressee. If you have received this email in error, please delete and advise the IT Help Desk at windsor.help.desk_at_windsorhealthgroup.com immediately.
  
  

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 29 2014 - 23:33:20 CET