Re: Oracle External Table / DAT file data problem

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 30 Jan 2014 09:26:20 -0600
Message-ID: <CAP79kiTdbPUhUiGjpsNO-=tk+Hy=omtnFFZ=8Yp9Ws1SbNWtbw_at_mail.gmail.com>

Thanks Kim - that's one option I hadn't considered. It's a pain to have to have to have more than 1 object to accomplish this, but this would keep me from having to modify the code base that does the load from external tables to the application tables.

Thanks for the input.

Chris

On Thu, Jan 30, 2014 at 1:16 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:

> 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 - 16:26:20 CET