Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Chibuzor.

Asked: March 08, 2018 - 5:46 pm UTC

Last updated: March 09, 2018 - 7:17 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

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)
)

and Connor said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.