• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2006
  • Last Modified:

lpad & trim in external table

Hi,

How to use trim & lpad fucntion in oracle external table?. I tried as below. It didn't work.

thanks
victor
CREATE TABLE diff
    (plan_ctry                      VARCHAR2(3),
    division                       VARCHAR2(2))
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  DPR_DATA_DIR
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
BADFILE DPR_LOG_DIR:'diff.bad'
DISCARDFILE DPR_LOG_DIR:'diff.dsc'
LOGFILE DPR_LOG_DIR:'diff.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
    (plan_ctry CHAR,                     
    division  CHAR "LPAD(TRIM(:DIVISION), 2, '0')"
  ))
   LOCATION (
    DPR_DATA_DIR:'ZISCFUOG1.CSV'
   )
  )
   REJECT LIMIT UNLIMITED
/

Open in new window

0
victory_in
Asked:
victory_in
1 Solution
 
schwertnerCommented:
I will suggest this when you read from the table

SELECT plan_ctry , LPAD(TRIM(:DIVISION), 2, '0') FROM diff;
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now