Help I am trying to use repace function on the Gender Column. The Column has only 2 possible values(-2,-3) and I am trying to replace those with M and F respectively. But I keep getting an error.
Record 5: Rejected - Error on table L2K_TNCPS_T_PERSON.
ORA-00913: too many values.
What am I doing wrong.
load data
infile *
truncate
into table L2K_TNCPS_T_PERSON
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
( PERSON_ID CHAR(4000),
SSN CHAR(4000),
LAST_NAME CHAR(4000) "TRIM(:LAST_NAME)",
FIRST_NAME CHAR(4000) "TRIM(:FIRST_NAME)",
MIDDLE_NAME CHAR(4000) "TRIM(:MIDDLE_NAME)",
IS_ORGANIZATION CHAR(4000),
DATE_OF_BIRTH DATE "RRRR-MM-DD",
GENDER CHAR(4000) "REPLACE(:GENDER,'-2','M'),REPLACE(:GENDER,'-3','F')",
ADDRESS_ID CHAR(4000),
GENDER_DESCR CHAR(4000)
)
We use the functions you specify to create an insert statement.
So in the most basic of SQL Loader control files, eg
(A,B,C)
we will generate an insert along the lines of:
insert into MY_TABLE (A,B,C) values (:A,:B,:C)
So in your code, we are ending up doing this:
insert into L2K_TNCPS_T_PERSON
(PERSON_ID,SSN, .... GENDER ... )
values
(:PERSON_ID,:SSN, .... REPLACE(:GENDER,'-2','M'),REPLACE(:GENDER,'-3','F')
So you can see that an SQL like that would not work... 'n' columns versus n+1 values.
You need to change your expression to:
replace(replace(:gender,'-2','M'),'-3','F')
so that it does two replacements but is still only a single expression