9i External Table - Upper Function possible?

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 3 Oct 2013 10:28:18 -0500
Message-ID: <CAP79kiTqSnHQfZ+hPYu8Zmn60J6UhE5qoFdPGKH5tYi2=7rEeg_at_mail.gmail.com>

I've got a table where I would like to apply an UPPER function in the ACCESS_PARAMETERS section on ICD9_CODE. SQLLDR can do this using "upper(:column_name)" but I'm not sure I can do this in an external table.  Has anyone (or does anyone) know the proper syntax to get an external table using ORACLE_LOADER to recognize the sqlldr UPPER(:col) functionality??
Thanks!

CREATE TABLE RDB_EXT.ICD9_CODES
(

  ICD9_CODE        VARCHAR2(10 BYTE),
  SPECIFIC         NUMBER(10),

  ICD9_SHORT_DESC VARCHAR2(25 BYTE),
  ICD9_LONG_DESC VARCHAR2(100 BYTE),

  EFFDATE          DATE,
  EXPDATE          DATE,
  AVL              NUMBER(3)

)

ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

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

BADFILE INSPRO_DATAFILE_OUTPUT:'ICD9_CODES.BAD' fields terminated by '|' MISSING FIELD VALUES ARE NULL (
ICD9_CODE CHAR(10),
SPECIFIC ,
ICD9_SHORT_DESC ,
ICD9_LONG_DESC ,
EFFDATE DATE "mm/dd/yyyy",
EXPDATE DATE "mm/dd/yyyy",
AVL
)

 )

     LOCATION (INSPRO_DATAFILE_DIR:'EXT-INSPRO-ICD9-Codes.DAT')   )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

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

Received on Thu Oct 03 2013 - 17:28:18 CEST