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

victory_inAsked:
Who is Participating?
 
schwertnerCommented:
I will suggest this when you read from the table

SELECT plan_ctry , LPAD(TRIM(:DIVISION), 2, '0') FROM diff;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here