Campaign failing with ORA-00913: too many values error
When running a campaign, requests use the "," as decimal point , there is a 00-913 oracle error (too many values) error.
Out parse_sql - status =  File: db_process.c Line: 6124
INSERT INTO MATOOL.CAMP_COMM_OUT_DET ccod ( A1 , CAMP_ID , CAMP_CYC , DET_ID , RUN_NUMBER , PAR_COMM_DET_ID , comm_status_id , keycode , comm_cost , pool_chan_type_id ) SELECT /*+ALL_ROWS*/ CS86_4_131_2.A1 , 131 , 1 , 2 , '1' , 0 , 0 , '' , 0,000 , NULL FROM MATOOL.CS86_4_131_2 CS86_4_131_2
In GVcompareVersion (6.7.0,3.1.1) File: GV.c Line: 7726
Out GVcompareVersion (1) File: GV.c Line: 7755
In db_declare_cursor(re parse is 1)
Oracle error -913
ORACLE ERROR = ORA-00913: too many values
ORA-00913: too many values
Steps to Reproduce
Run a campaign.
Campaign was failing while inserting data ‘0,000’ for the column ‘comm_cost’ into the CAMP_COMM_OUT_DET table.
Oracle is treating the value as two different values due to Character set related parameter values mismatch, hence INSERT is failing.
Environment parameter settings regarding Character set are suggested to be changed as follows:
- Set VenvNLSLang parameter in Vantage.init to “AMERICAN_AMERICA.WE8MSWIN1252”
- In .profile add “export NLS_NUMERIC_CHARACTERS='.,' ”
Currently there is no ORA-00913 issue reported in executing the particular campaign. Therefore, confirmed closure.
Published August 18, 2016 - Updated September 8, 2016