Re: Oracle External Table / DAT file data problem
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: christopherdtaylor1994@xxxxxxxxx
- Date: Thu, 30 Jan 2014 08:16:34 +0100
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@xxxxxxxxx
@kibeha
On Wed, Jan 29, 2014 at 11:33 PM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> 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.
>
>
Other related posts:
