Oracle External Table / DAT file data problem

  • From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 Jan 2014 16:33:20 -0600

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@xxxxxxxxxxxxxxxxxxxxxx immediately.

JPEG image

Other related posts: