Re: Oracle External Table / DAT file data problem

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Thu, 30 Jan 2014 08:16:34 +0100
Message-ID: <CA+S=qd2aJPLp4in3z_aoL7Pups1L3NsMFyn3mvbcBerjDLnRDg_at_mail.gmail.com>

Hi, Chris

I don't believe you can do such manipulations in the external table definition. Most likely they have not bothered to do so, as the workaround is fairly easy:

Make your external table in a different name and define the columns as varchars.
Then create a view named what the table was before, and do the manipulation in the view.
Something like (untested code) :

CREATE TABLE RDB_EXT.COMM_ACCTG_HIERARCHY*_EXT* (

  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*     VARCHAR2(8)*,
  HR_CONT_END_DATE*       VARCHAR2(8)*

)

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' fieldsterminated 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  ,
HR_CONT_END_DATE DATE  "

)
)

     LOCATION (INSPRO_DATAFILE_DIR:'EXT-INSPRO-COMM-HIERARC.DAT')   )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING; CREATE VIEW RDB_EXT.COMM_ACCTG_HIERARCHY AS
SELECT

  HR_COMMISSION_DATE     ,
  HR_RECORD_NUM          ,
  HR_COMM_ACCT_LINE_NUM  ,
  HR_LOG_DATE            ,
  HR_RANK                ,
  HR_AGENT_NUMBER        ,
  HR_RATE_LEVEL          ,

* TO_DATE(REPLACE(*HR_CONT_START_DATE,'00000000'),'YYYYMMDD') HR_CONT_START_DATE,
  *TO_DATE(REPLACE(*HR_CONT_END_DATE ,'00000000'),'YYYYMMDD') HR_CONT_END_DATE
FROM RDB_EXT.COMM_ACCTG_HIERARCHY*_EXT;* Use a view on top of the external table to do all the SQL manipulation you desire ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Wed, Jan 29, 2014 at 11:33 PM, Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> 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.
>
>

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

Received on Thu Jan 30 2014 - 08:16:34 CET