Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Insert all in Oracle10g (Oracle 10g)
Problem in Insert all in Oracle10g [message #356169] Wed, 29 October 2008 16:38 Go to next message
sspn2010
Messages: 162
Registered: October 2008
Senior Member
Hi,

Recently we have migrated database from oracle 9i to 1og. After migrating to 10g we are facing an issue with INSERT ALL statement. Statement worked fine in 9i. Please see the attached statement. Would appreciate if someone helps on this issue.

INSERT /* + append */ ALL
WHEN v_call_disp IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,5,8,'17148',8,'21-OCT-08')
WHEN v_test_fu IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,320,8,'17148',8,'21-OCT-08')
WHEN v_changefu IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,321,8,'17148',8,'21-OCT-08')
WHEN v_sat_new IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,322,8,'17148',8,'21-OCT-08')
WHEN v_lancet_device IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,325,8,'17148',8,'21-OCT-08')
WHEN v_refer2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,64,8,'17148',8,'21-OCT-08')
WHEN v_refer IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,63,8,'17148',8,'21-OCT-08')
WHEN v_c9 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,38,8,'17148',8,'21-OCT-08')
WHEN v_c17 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,44,8,'17148',8,'21-OCT-08')
WHEN v_c18 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,45,8,'17148',8,'21-OCT-08')
WHEN v_c19 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,46,8,'17148',8,'21-OCT-08')
WHEN v_c22 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,49,8,'17148',8,'21-OCT-08')
WHEN v_c23 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,50,8,'17148',8,'21-OCT-08')
WHEN v_c26 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,51,8,'17148',8,'21-OCT-08')
WHEN v_c27 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,52,8,'17148',8,'21-OCT-08')
WHEN v_c28 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,53,8,'17148',8,'21-OCT-08')
WHEN v_c29 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,54,8,'17148',8,'21-OCT-08')
WHEN v_c30 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,55,8,'17148',8,'21-OCT-08')
WHEN v_amputation IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,68,8,'17148',8,'21-OCT-08')
WHEN v_blind IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,69,8,'17148',8,'21-OCT-08')
WHEN v_c7 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,36,8,'17148',8,'21-OCT-08')
WHEN v_c6 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,35,8,'17148',8,'21-OCT-08')
WHEN v_c5 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,34,8,'17148',8,'21-OCT-08')
WHEN v_c4 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,33,8,'17148',8,'21-OCT-08')
WHEN v_c3 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,32,8,'17148',8,'21-OCT-08')
WHEN v_c2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,31,8,'17148',8,'21-OCT-08')
WHEN v_c1 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,30,8,'17148',8,'21-OCT-08')
WHEN v_c8 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,37,8,'17148',8,'21-OCT-08')
WHEN v_email_per2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,61,8,'17148',8,'21-OCT-08')
WHEN v_net_access IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,60,8,'17148',8,'21-OCT-08')
WHEN v_new_gender IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,67,8,'17148',8,'21-OCT-08')
WHEN v_mo_diagnos IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,242,8,'17148',8,'21-OCT-08')
WHEN v_new_birthm IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,65,8,'17148',8,'21-OCT-08')
WHEN v_s4 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,161,8,'17148',8,'21-OCT-08')
WHEN v_s3 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,160,8,'17148',8,'21-OCT-08')
WHEN v_s2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,159,8,'17148',8,'21-OCT-08')
WHEN v_s1 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,158,8,'17148',8,'21-OCT-08')
WHEN v_s0 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,157,8,'17148',8,'21-OCT-08')
WHEN v_sys1 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,95,8,'17148',8,'21-OCT-08')
WHEN v_sys1a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,96,8,'17148',8,'21-OCT-08')
WHEN v_sys2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,97,8,'17148',8,'21-OCT-08')
WHEN v_sys2a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,98,8,'17148',8,'21-OCT-08')
WHEN v_sys3 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,99,8,'17148',8,'21-OCT-08')
WHEN v_sys3a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,100,8,'17148',8,'21-OCT-08')
WHEN v_sys3b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,101,8,'17148',8,'21-OCT-08')
WHEN v_sys3c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,102,8,'17148',8,'21-OCT-08')
WHEN v_sys3d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,103,8,'17148',8,'21-OCT-08')
WHEN v_sys5 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,105,8,'17148',8,'21-OCT-08')
WHEN v_sys6 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,106,8,'17148',8,'21-OCT-08')
WHEN v_sys6a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,107,8,'17148',8,'21-OCT-08')
WHEN v_sys6b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,108,8,'17148',8,'21-OCT-08')
WHEN v_sys6c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,109,8,'17148',8,'21-OCT-08')
WHEN v_sys6d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,110,8,'17148',8,'21-OCT-08')
WHEN v_sys7 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,111,8,'17148',8,'21-OCT-08')
WHEN v_sys7a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,112,8,'17148',8,'21-OCT-08')
WHEN v_sys7b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,113,8,'17148',8,'21-OCT-08')
WHEN v_sys7c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,114,8,'17148',8,'21-OCT-08')
WHEN v_sys7d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,115,8,'17148',8,'21-OCT-08')
WHEN v_sys7e IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,116,8,'17148',8,'21-OCT-08')
WHEN v_sys7f IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,117,8,'17148',8,'21-OCT-08')
WHEN v_sys8 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,118,8,'17148',8,'21-OCT-08')
WHEN v_sys8a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,119,8,'17148',8,'21-OCT-08')
WHEN v_sys8b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,120,8,'17148',8,'21-OCT-08')
WHEN v_sys8c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,121,8,'17148',8,'21-OCT-08')
WHEN v_sys9 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,122,8,'17148',8,'21-OCT-08')
WHEN v_sys9a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,123,8,'17148',8,'21-OCT-08')
WHEN v_sys9b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,124,8,'17148',8,'21-OCT-08')
WHEN v_sys9c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,125,8,'17148',8,'21-OCT-08')
WHEN v_sys10 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,126,8,'17148',8,'21-OCT-08')
WHEN v_sys11 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,127,8,'17148',8,'21-OCT-08')
WHEN v_sys12 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,128,8,'17148',8,'21-OCT-08')
WHEN v_sys12a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,129,8,'17148',8,'21-OCT-08')
WHEN v_sys12b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,130,8,'17148',8,'21-OCT-08')
WHEN v_sys12c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,131,8,'17148',8,'21-OCT-08')
WHEN v_sys12d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,132,8,'17148',8,'21-OCT-08')
WHEN v_sys12e IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,133,8,'17148',8,'21-OCT-08')
WHEN v_sys13 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,134,8,'17148',8,'21-OCT-08')
WHEN v_sys14 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,135,8,'17148',8,'21-OCT-08')
WHEN v_sys14a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,136,8,'17148',8,'21-OCT-08')
WHEN v_sys14b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,137,8,'17148',8,'21-OCT-08')
WHEN v_sys14c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,138,8,'17148',8,'21-OCT-08')
WHEN v_sys14d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,139,8,'17148',8,'21-OCT-08')
WHEN v_sys14e IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,140,8,'17148',8,'21-OCT-08')
WHEN v_sys15 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,141,8,'17148',8,'21-OCT-08')
WHEN v_sys15a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,142,8,'17148',8,'21-OCT-08')
WHEN v_sys16 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,143,8,'17148',8,'21-OCT-08')
WHEN v_sys17 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,144,8,'17148',8,'21-OCT-08')
WHEN v_sys19 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,145,8,'17148',8,'21-OCT-08')
WHEN v_sys19a IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,146,8,'17148',8,'21-OCT-08')
WHEN v_sys19b IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,147,8,'17148',8,'21-OCT-08')
WHEN v_sys19c IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,148,8,'17148',8,'21-OCT-08')
WHEN v_sys19d IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,149,8,'17148',8,'21-OCT-08')
WHEN v_sys19e IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,150,8,'17148',8,'21-OCT-08')
WHEN v_sys19f IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,151,8,'17148',8,'21-OCT-08')
WHEN v_sys19g IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,152,8,'17148',8,'21-OCT-08')
WHEN v_sys19h IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,153,8,'17148',8,'21-OCT-08')
WHEN v_sys19i IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,154,8,'17148',8,'21-OCT-08')
WHEN v_sys20 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,155,8,'17148',8,'21-OCT-08')
WHEN v_why_not2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,324,8,'17148',8,'21-OCT-08')
WHEN v_whyno_m IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,323,8,'17148',8,'21-OCT-08')

WHEN v_prob_feet8 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,188,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet9 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,189,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet7 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,187,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet5 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,185,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet4 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,184,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet3 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,183,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,182,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet1 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,181,8,'17148',8,'21-OCT-08')
WHEN v_prob_feet IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,180,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes5 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,178,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes4 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,177,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes3 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,176,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes2 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,175,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes1 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,174,8,'17148',8,'21-OCT-08')
WHEN v_prob_eyes IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,173,8,'17148',8,'21-OCT-08')
WHEN v_s8 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,165,8,'17148',8,'21-OCT-08')
WHEN v_s7 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,164,8,'17148',8,'21-OCT-08')
WHEN v_s6 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,163,8,'17148',8,'21-OCT-08')
WHEN v_s5 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,162,8,'17148',8,'21-OCT-08')
WHEN v_s9 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,166,8,'17148',8,'21-OCT-08')
WHEN v_s10 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,167,8,'17148',8,'21-OCT-08')
WHEN v_s13 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,170,8,'17148',8,'21-OCT-08')
WHEN v_s11 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,168,8,'17148',8,'21-OCT-08')
WHEN v_s12 IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,169,8,'17148',8,'21-OCT-08')
WHEN v_freq IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,171,8,'17148',8,'21-OCT-08')
WHEN v_eyes IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,172,8,'17148',8,'21-OCT-08')
WHEN v_feet IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,179,8,'17148',8,'21-OCT-08')
WHEN v_hbac IS NOT NULL THEN INTO validation_record_error_tbl(record_key,column_key,error_key,file_id,process_key,process_dt) values(consumer_id,190,8,'17148',8,'21-OCT-08')
SELECT consumer_id,src_tbl.call_disp s_call_disp,call_disp_lkp.call_disposition lkp_call_disp
,(case when src_tbl.call_disp is not null and call_disp_lkp.call_disposition is null then 1 else null end) v_call_disp
,src_tbl.test_fu s_test_fu,test_fu_lkp.code_in lkp_test_fu
,(case when src_tbl.test_fu is not null and test_fu_lkp.code_in is null then 1 else null end) v_test_fu
,src_tbl.changefu s_changefu,changefu_lkp.code_in lkp_changefu
,(case when src_tbl.changefu is not null and changefu_lkp.code_in is null then 1 else null end) v_changefu
,src_tbl.sat_new s_sat_new,sat_new_lkp.code_in lkp_sat_new
,(case when src_tbl.sat_new is not null and sat_new_lkp.code_in is null then 1 else null end) v_sat_new
,src_tbl.lancet_device s_lancet_device,lancet_device_lkp.code_in lkp_lancet_device
,(case when src_tbl.lancet_device is not null and lancet_device_lkp.code_in is null then 1 else null end) v_lancet_device
,src_tbl.refer2 s_refer2,refer2_lkp.code_in lkp_refer2
,(case when src_tbl.refer2 is not null and refer2_lkp.code_in is null then 1 else null end) v_refer2
,src_tbl.refer s_refer,refer_lkp.code_in lkp_refer
,(case when src_tbl.refer is not null and refer_lkp.code_in is null then 1 else null end) v_refer
,src_tbl.c9 s_c9,(case when src_tbl.c9 not in('X','0') then 5 else null end) v_c9
,src_tbl.c17 s_c17,(case when src_tbl.c17 not in('X','0') then 5 else null end) v_c17
,src_tbl.c18 s_c18,(case when src_tbl.c18 not in('X','0') then 5 else null end) v_c18
,src_tbl.c19 s_c19,(case when src_tbl.c19 not in('X','0') then 5 else null end) v_c19
,src_tbl.c22 s_c22,(case when src_tbl.c22 not in('X','0') then 5 else null end) v_c22
,src_tbl.c23 s_c23,(case when src_tbl.c23 not in('X','0') then 5 else null end) v_c23
,src_tbl.c26 s_c26,(case when src_tbl.c26 not in('X','0') then 5 else null end) v_c26
,src_tbl.c27 s_c27,(case when src_tbl.c27 not in('X','0') then 5 else null end) v_c27
,src_tbl.c28 s_c28,(case when src_tbl.c28 not in('X','0') then 5 else null end) v_c28
,src_tbl.c29 s_c29,(case when src_tbl.c29 not in('X','0') then 5 else null end) v_c29
,src_tbl.c30 s_c30,(case when src_tbl.c30 not in('X','0') then 5 else null end) v_c30
,src_tbl.amputation s_amputation,(case when src_tbl.amputation not in('X','0') then 5 else null end) v_amputation
,src_tbl.blind s_blind,(case when src_tbl.blind not in('X','0') then 5 else null end) v_blind
,src_tbl.c7 s_c7,(case when src_tbl.c7 not in('X','0') then 5 else null end) v_c7
,src_tbl.c6 s_c6,(case when src_tbl.c6 not in('X','0') then 5 else null end) v_c6
,src_tbl.c5 s_c5,(case when src_tbl.c5 not in('X','0') then 5 else null end) v_c5
,src_tbl.c4 s_c4,(case when src_tbl.c4 not in('X','0') then 5 else null end) v_c4
,src_tbl.c3 s_c3,(case when src_tbl.c3 not in('X','0') then 5 else null end) v_c3
,src_tbl.c2 s_c2,(case when src_tbl.c2 not in('X','0') then 5 else null end) v_c2
,src_tbl.c1 s_c1,(case when src_tbl.c1 not in('X','0') then 5 else null end) v_c1
,src_tbl.c8 s_c8,(case when src_tbl.c8 not in('X','0') then 5 else null end) v_c8
,src_tbl.email_per2 s_email_per2,(case when src_tbl.email_per2 not in('0','1') then 5 else null end) v_email_per2
,src_tbl.prob_feet8 s_prob_feet8,(case when src_tbl.prob_feet8 not in('0','1') then 5 else null end) v_prob_feet8
,src_tbl.prob_feet9 s_prob_feet9,(case when src_tbl.prob_feet9 not in('0','1') then 5 else null end) v_prob_feet9
,src_tbl.prob_feet7 s_prob_feet7,(case when src_tbl.prob_feet7 not in('0','1') then 5 else null end) v_prob_feet7
,src_tbl.prob_feet5 s_prob_feet5,(case when src_tbl.prob_feet5 not in('0','1') then 5 else null end) v_prob_feet5
,src_tbl.prob_feet4 s_prob_feet4,(case when src_tbl.prob_feet4 not in('0','1') then 5 else null end) v_prob_feet4
,src_tbl.prob_feet3 s_prob_feet3,(case when src_tbl.prob_feet3 not in('0','1') then 5 else null end) v_prob_feet3
,src_tbl.prob_feet2 s_prob_feet2,(case when src_tbl.prob_feet2 not in('0','1') then 5 else null end) v_prob_feet2
,src_tbl.prob_feet1 s_prob_feet1,(case when src_tbl.prob_feet1 not in('0','1') then 5 else null end) v_prob_feet1
,src_tbl.prob_feet s_prob_feet,(case when src_tbl.prob_feet not in('0','1') then 5 else null end) v_prob_feet
,src_tbl.prob_eyes5 s_prob_eyes5,(case when src_tbl.prob_eyes5 not in('0','1') then 5 else null end) v_prob_eyes5
,src_tbl.prob_eyes4 s_prob_eyes4,(case when src_tbl.prob_eyes4 not in('0','1') then 5 else null end) v_prob_eyes4
,src_tbl.prob_eyes3 s_prob_eyes3,(case when src_tbl.prob_eyes3 not in('0','1') then 5 else null end) v_prob_eyes3
,src_tbl.prob_eyes2 s_prob_eyes2,(case when src_tbl.prob_eyes2 not in('0','1') then 5 else null end) v_prob_eyes2
,src_tbl.prob_eyes1 s_prob_eyes1,(case when src_tbl.prob_eyes1 not in('0','1') then 5 else null end) v_prob_eyes1
,src_tbl.prob_eyes s_prob_eyes,(case when src_tbl.prob_eyes not in('0','1') then 5 else null end) v_prob_eyes
,src_tbl.net_access s_net_access,(case when src_tbl.net_access not in('0','1','2') then 5 else null end) v_net_access
,src_tbl.new_gender s_new_gender,(case when src_tbl.new_gender not in('M','F',chr(0)) then 5 else null end) v_new_gender
,src_tbl.mo_diagnos s_mo_diagnos,(case when src_tbl.mo_diagnos not in('01','02','03','04','05','06','07','08','09','10','11','12','00','1','2','3','4','5','6','7','8',
'9') then 5 else null end) v_mo_diagnos
,src_tbl.new_birthm s_new_birthm,(case when src_tbl.new_birthm not in('01','02','03','04','05','06','07','08','09','10','11','12','00','1','2','3','4','5','6','7','8',
'9') then 5 else null end) v_new_birthm
,src_tbl.s8 s_s8,(case when src_tbl.s8 not in('1') then 5 else null end) v_s8
,src_tbl.s7 s_s7,(case when src_tbl.s7 not in('1') then 5 else null end) v_s7
,src_tbl.s6 s_s6,(case when src_tbl.s6 not in('1') then 5 else null end) v_s6
,src_tbl.s5 s_s5,(case when src_tbl.s5 not in('1') then 5 else null end) v_s5
,src_tbl.s4 s_s4,(case when src_tbl.s4 not in('1') then 5 else null end) v_s4
,src_tbl.s3 s_s3,(case when src_tbl.s3 not in('1') then 5 else null end) v_s3
,src_tbl.s2 s_s2,(case when src_tbl.s2 not in('1') then 5 else null end) v_s2
,src_tbl.s1 s_s1,(case when src_tbl.s1 not in('1') then 5 else null end) v_s1
,src_tbl.s0 s_s0,(case when src_tbl.s0 not in('1') then 5 else null end) v_s0
,src_tbl.s9 s_s9,(case when src_tbl.s9 not in('1') then 5 else null end) v_s9
,src_tbl.s10 s_s10,(case when src_tbl.s10 not in('1') then 5 else null end) v_s10
,src_tbl.s13 s_s13,(case when src_tbl.s13 not in('1') then 5 else null end) v_s13
,src_tbl.s11 s_s11,(case when src_tbl.s11 not in('1') then 5 else null end) v_s11
,src_tbl.s12 s_s12,(case when src_tbl.s12 not in('1') then 5 else null end) v_s12
,src_tbl.sys1 s_sys1,(case when src_tbl.sys1 not in('A','B') then 5 else null end) v_sys1
,src_tbl.sys1a s_sys1a,(case when src_tbl.sys1a not in('A','B') then 5 else null end) v_sys1a
,src_tbl.sys2 s_sys2,(case when src_tbl.sys2 not in('A','B') then 5 else null end) v_sys2
,src_tbl.sys2a s_sys2a,(case when src_tbl.sys2a not in('A','B') then 5 else null end) v_sys2a
,src_tbl.sys3 s_sys3,(case when src_tbl.sys3 not in('A','B') then 5 else null end) v_sys3
,src_tbl.sys3a s_sys3a,(case when src_tbl.sys3a not in('A','B') then 5 else null end) v_sys3a
,src_tbl.sys3b s_sys3b,(case when src_tbl.sys3b not in('A','B') then 5 else null end) v_sys3b
,src_tbl.sys3c s_sys3c,(case when src_tbl.sys3c not in('A','B') then 5 else null end) v_sys3c
,src_tbl.sys3d s_sys3d,(case when src_tbl.sys3d not in('A','B') then 5 else null end) v_sys3d
,src_tbl.sys5 s_sys5,(case when src_tbl.sys5 not in('A','B') then 5 else null end) v_sys5
,src_tbl.sys6 s_sys6,(case when src_tbl.sys6 not in('A','B') then 5 else null end) v_sys6
,src_tbl.sys6a s_sys6a,(case when src_tbl.sys6a not in('A','B') then 5 else null end) v_sys6a
,src_tbl.sys6b s_sys6b,(case when src_tbl.sys6b not in('A','B') then 5 else null end) v_sys6b
,src_tbl.sys6c s_sys6c,(case when src_tbl.sys6c not in('A','B') then 5 else null end) v_sys6c
,src_tbl.sys6d s_sys6d,(case when src_tbl.sys6d not in('A','B') then 5 else null end) v_sys6d
,src_tbl.sys7 s_sys7,(case when src_tbl.sys7 not in('A','B') then 5 else null end) v_sys7
,src_tbl.sys7a s_sys7a,(case when src_tbl.sys7a not in('A','B') then 5 else null end) v_sys7a
,src_tbl.sys7b s_sys7b,(case when src_tbl.sys7b not in('A','B') then 5 else null end) v_sys7b
,src_tbl.sys7c s_sys7c,(case when src_tbl.sys7c not in('A','B') then 5 else null end) v_sys7c
,src_tbl.sys7d s_sys7d,(case when src_tbl.sys7d not in('A','B') then 5 else null end) v_sys7d
,src_tbl.sys7e s_sys7e,(case when src_tbl.sys7e not in('A','B') then 5 else null end) v_sys7e
,src_tbl.sys7f s_sys7f,(case when src_tbl.sys7f not in('A','B') then 5 else null end) v_sys7f
,src_tbl.sys8 s_sys8,(case when src_tbl.sys8 not in('A','B') then 5 else null end) v_sys8
,src_tbl.sys8a s_sys8a,(case when src_tbl.sys8a not in('A','B') then 5 else null end) v_sys8a
,src_tbl.sys8b s_sys8b,(case when src_tbl.sys8b not in('A','B') then 5 else null end) v_sys8b
,src_tbl.sys8c s_sys8c,(case when src_tbl.sys8c not in('A','B') then 5 else null end) v_sys8c
,src_tbl.sys9 s_sys9,(case when src_tbl.sys9 not in('A','B') then 5 else null end) v_sys9
,src_tbl.sys9a s_sys9a,(case when src_tbl.sys9a not in('A','B') then 5 else null end) v_sys9a
,src_tbl.sys9b s_sys9b,(case when src_tbl.sys9b not in('A','B') then 5 else null end) v_sys9b
,src_tbl.sys9c s_sys9c,(case when src_tbl.sys9c not in('A','B') then 5 else null end) v_sys9c
,src_tbl.sys10 s_sys10,(case when src_tbl.sys10 not in('A','B') then 5 else null end) v_sys10
,src_tbl.sys11 s_sys11,(case when src_tbl.sys11 not in('A','B') then 5 else null end) v_sys11
,src_tbl.sys12 s_sys12,(case when src_tbl.sys12 not in('A','B') then 5 else null end) v_sys12
,src_tbl.sys12a s_sys12a,(case when src_tbl.sys12a not in('A','B') then 5 else null end) v_sys12a
,src_tbl.sys12b s_sys12b,(case when src_tbl.sys12b not in('A','B') then 5 else null end) v_sys12b
,src_tbl.sys12c s_sys12c,(case when src_tbl.sys12c not in('A','B') then 5 else null end) v_sys12c
,src_tbl.sys12d s_sys12d,(case when src_tbl.sys12d not in('A','B') then 5 else null end) v_sys12d
,src_tbl.sys12e s_sys12e,(case when src_tbl.sys12e not in('A','B') then 5 else null end) v_sys12e
,src_tbl.sys13 s_sys13,(case when src_tbl.sys13 not in('A','B') then 5 else null end) v_sys13
,src_tbl.sys14 s_sys14,(case when src_tbl.sys14 not in('A','B') then 5 else null end) v_sys14
,src_tbl.sys14a s_sys14a,(case when src_tbl.sys14a not in('A','B') then 5 else null end) v_sys14a
,src_tbl.sys14b s_sys14b,(case when src_tbl.sys14b not in('A','B') then 5 else null end) v_sys14b
,src_tbl.sys14c s_sys14c,(case when src_tbl.sys14c not in('A','B') then 5 else null end) v_sys14c
,src_tbl.sys14d s_sys14d,(case when src_tbl.sys14d not in('A','B') then 5 else null end) v_sys14d
,src_tbl.sys14e s_sys14e,(case when src_tbl.sys14e not in('A','B') then 5 else null end) v_sys14e
,src_tbl.sys15 s_sys15,(case when src_tbl.sys15 not in('A','B') then 5 else null end) v_sys15
,src_tbl.sys15a s_sys15a,(case when src_tbl.sys15a not in('A','B') then 5 else null end) v_sys15a
,src_tbl.sys16 s_sys16,(case when src_tbl.sys16 not in('A','B') then 5 else null end) v_sys16
,src_tbl.sys17 s_sys17,(case when src_tbl.sys17 not in('A','B') then 5 else null end) v_sys17
,src_tbl.sys19 s_sys19,(case when src_tbl.sys19 not in('A','B') then 5 else null end) v_sys19
,src_tbl.sys19a s_sys19a,(case when src_tbl.sys19a not in('A','B') then 5 else null end) v_sys19a
,src_tbl.sys19b s_sys19b,(case when src_tbl.sys19b not in('A','B') then 5 else null end) v_sys19b
,src_tbl.sys19c s_sys19c,(case when src_tbl.sys19c not in('A','B') then 5 else null end) v_sys19c
,src_tbl.sys19d s_sys19d,(case when src_tbl.sys19d not in('A','B') then 5 else null end) v_sys19d
,src_tbl.sys19e s_sys19e,(case when src_tbl.sys19e not in('A','B') then 5 else null end) v_sys19e
,src_tbl.sys19f s_sys19f,(case when src_tbl.sys19f not in('A','B') then 5 else null end) v_sys19f
,src_tbl.sys19g s_sys19g,(case when src_tbl.sys19g not in('A','B') then 5 else null end) v_sys19g
,src_tbl.sys19h s_sys19h,(case when src_tbl.sys19h not in('A','B') then 5 else null end) v_sys19h
,src_tbl.sys19i s_sys19i,(case when src_tbl.sys19i not in('A','B') then 5 else null end) v_sys19i
,src_tbl.sys20 s_sys20,(case when src_tbl.sys20 not in('A','B') then 5 else null end) v_sys20
,src_tbl.why_not2 s_why_not2,why_not2_lkp.code_in lkp_why_not2
,(case when src_tbl.why_not2 is not null and why_not2_lkp.code_in is null then 1 else null end) v_why_not2
,src_tbl.whyno_m s_whyno_m,whyno_m_lkp.code_in lkp_whyno_m
,(case when src_tbl.whyno_m is not null and whyno_m_lkp.code_in is null then 1 else null end) v_whyno_m
,src_tbl.freq s_freq,freq_lkp.code_in lkp_freq
,(case when src_tbl.freq is not null and freq_lkp.code_in is null then 1 else null end) v_freq
,src_tbl.eyes s_eyes,eyes_lkp.code_in lkp_eyes
,(case when src_tbl.eyes is not null and eyes_lkp.code_in is null then 1 else null end) v_eyes
,src_tbl.feet s_feet,feet_lkp.code_in lkp_feet
,(case when src_tbl.feet is not null and feet_lkp.code_in is null then 1 else null end) v_feet
,src_tbl.hbac s_hbac,hbac_lkp.code_in lkp_hbac
,(case when src_tbl.hbac is not null and hbac_lkp.code_in is null then 1 else null end) v_hbac
FROM (select * FROM (select c.*, count(*) over (partition by consumer_id) record_cnt
FROM CARE_RB2_RESP_PRELOAD c where vendor_key=4 AND consumer_id IS NOT NULL ) WHERE record_cnt=1) src_tbl,call_disposition_codes call_disp_lkp
,(SELECT * FROM btf_ref WHERE EXT_SOURCE_NM in('NEW-MARTIN') or code_in='9') test_fu_lkp
,What_Changed_Not_Testing_Ref changefu_lkp
,Meter_Satisfaction_Ref sat_new_lkp
,Lancet_Code_Ref lancet_device_lkp
,refer_code_ref refer2_lkp
,refer_code_ref refer_lkp
,doctor_test_freq_ref freq_lkp
,eye_test_freq_ref eyes_lkp
,feet_test_freq_ref feet_lkp
,HBAC_test_freq_ref hbac_lkp
,why_not_ref why_not2_lkp
,why_not_ref whyno_m_lkp
WHERE src_tbl.call_disp=call_disp_lkp.call_disposition(+)
and src_tbl.test_fu=test_fu_lkp.code_in(+)
and src_tbl.changefu=changefu_lkp.code_in(+)
and src_tbl.sat_new=sat_new_lkp.code_in(+)
and src_tbl.lancet_device=lancet_device_lkp.code_in(+)
and src_tbl.refer2=refer2_lkp.code_in(+)
and src_tbl.refer=refer_lkp.code_in(+)
and src_tbl.freq=freq_lkp.code_in(+)
and src_tbl.eyes=eyes_lkp.code_in(+)
and src_tbl.feet=feet_lkp.code_in(+)
and src_tbl.hbac=hbac_lkp.code_in(+)
and src_tbl.why_not2=why_not2_lkp.code_in(+)
and src_tbl.whyno_m=whyno_m_lkp.code_in(+)

Thanks
Srinath
Re: Problem in Insert all in Oracle10g [message #356185 is a reply to message #356169] Wed, 29 October 2008 19:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Is the power cord plugged in to the wall?

Kevin
Re: Problem in Insert all in Oracle10g [message #356195 is a reply to message #356169] Wed, 29 October 2008 22:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
we are facing an issue with INSERT ALL statement. Statement worked fine in 9i.

Unfortunately, you did not specify, what the "issue" is at all and what "working fine" means.
Let me guess the issue: it does nothing, it messes up data, it throws some obscure error. Sorry, I have no other idea, but maybe others maybe more imaginative.
So, the only thing I may do is to cross the fingers that you overcome this. Good luck!
Re: Problem in Insert all in Oracle10g [message #356196 is a reply to message #356169] Wed, 29 October 2008 22:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Format the code properly and tell us what the error you got .
Also format the date properly while inserting .

Smile
Rajuvan.

[Updated on: Wed, 29 October 2008 22:49]

Report message to a moderator

Re: Problem in Insert all in Oracle10g [message #356328 is a reply to message #356185] Thu, 30 October 2008 06:03 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Only error I can guess will be this statement. Also remove the blank line in between your insert statements.
Quote:

WHEN v_call_disp IS NOT NULL THEN INTO validation_record_error_tbl(...,process_dt) values(...,'21-OCT-08')


I believev process_dt is a date column and you are inserting a string into a date column. Oracle has to do the conversion to a date and probably you nls setting could have changed. So this is one of the reason why everybody stress if you are inserting a date you explicitly convert it using a to_Date format rather than relying on the implicit conversion.

If this is not the case it will be really helpful if you could format your post and copy & paste the actual error.

Regards

Raj

[Edit:] Apologies I overlooked @Rajuvan's post.

[Updated on: Thu, 30 October 2008 06:10]

Report message to a moderator

Re: Problem in Insert all in Oracle10g [message #356348 is a reply to message #356169] Thu, 30 October 2008 08:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I think all this talk is premature, until he checks to see if it is plugged in.

Kevin
Re: Problem in Insert all in Oracle10g [message #356360 is a reply to message #356348] Thu, 30 October 2008 08:54 Go to previous messageGo to next message
sspn2010
Messages: 162
Registered: October 2008
Senior Member
Hi Guys,

Sorry, i forgot to mention the error which i'm getting.

Getting below error.

ORA-00913: TOO MANY VALUES.

But the same statement is working fine i.e inserting records in table, but in 10g i'm getting the above error.

Can someone help me.

Thanks
Srinath
Re: Problem in Insert all in Oracle10g [message #356362 is a reply to message #356169] Thu, 30 October 2008 09:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This gives the two most common reasons for this error:

Oracle/PLSQL: ORA-00913 Error

I am guessing one of three things:

Quote:
1) you have one too many commas in one of those inserts
2) there is some other error that is confusing oracle and this was the best he could do
3) the other poster was right, and the blank line is messing you up

I would suggest you start on #3, then #1.

For #1, try doing small subsets of the inserts till you find the one that is hosing you.

Kevin
Re: Problem in Insert all in Oracle10g [message #356368 is a reply to message #356169] Thu, 30 October 2008 09:24 Go to previous messageGo to next message
sspn2010
Messages: 162
Registered: October 2008
Senior Member
Hi,

Thank you for your prompt reply..

This Insert statement is executing successfully if i comment some of WHEN clauses. Is there any limitation in 10g on using WHEN clause in INSERT ALL.

Thanks
Srinath
Re: Problem in Insert all in Oracle10g [message #356373 is a reply to message #356368] Thu, 30 October 2008 09:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

This Insert statement is executing successfully if i comment some of WHEN clauses


How do we know which statements you have commented.

Unfortunately none of us can see what exactly you are doing. So it will be really helpful if you could copy and paste the entire statement along with the error. I repeat it again. Copy and paste it along with the proper code tags.

Regards

Raj
Re: Problem in Insert all in Oracle10g [message #356375 is a reply to message #356169] Thu, 30 October 2008 09:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
[Edited: Sorry, mistaken the WHEN clause with the CASE expression]

However the INSERT statement is documented as well, so you may start exploring the documentation, available e.g. online on http://tahiti.oracle.com/

[Updated on: Thu, 30 October 2008 09:46]

Report message to a moderator

Re: Problem in Insert all in Oracle10g [message #356379 is a reply to message #356375] Thu, 30 October 2008 09:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OP is not referring to the WHEN clause of the CASE expression. OP is referring to the WHEN clause of the INSERT ALL statement.

As for a limit on the number of WHEN lines, I could not immediately find any. I would suggest a review of the documentation on INSERT ALL, and/or a TAR posted with oracle support asking the question.

Also, do not discount the possibility of an error in the lines you commented out. Let me suggest you try running those lines instead of the ones you left in (eg reverse you commentss). This should give some insight as to if there is a line limit, or if there is a bug on the lines you commented out.

Good luck, Kevin
Re: Problem in Insert all in Oracle10g [message #356451 is a reply to message #356375] Thu, 30 October 2008 14:05 Go to previous messageGo to next message
sspn2010
Messages: 162
Registered: October 2008
Senior Member
Hi Raj,

I'm trying to execute the attached Insert statement in 10g and getting the error ORA-00913 Too many Values.

But this insert statement executed successfully in oracle 9i. Is there any limitations in oracle 10g if we use INSERT ALL.

Please let me know if you need any information about my Database.

Thanks
Srinath

Re: Problem in Insert all in Oracle10g [message #356460 is a reply to message #356451] Thu, 30 October 2008 15:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
sminnakanti wrote on Thu, 30 October 2008 20:05
Please let me know if you need any information about my Database.


Yes. We need you to post the entire SQL*Plus session including the error.

Formatted



Re: Problem in Insert all in Oracle10g [message #356518 is a reply to message #356169] Fri, 31 October 2008 00:01 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Did you bother to read the Oracle documentation, as suggested before?

SQL Reference
Chapter 16 SQL Statements
INSERT
conditional_insert_clause
Quote:
A single multitable insert statement can contain up to 127 WHEN clauses.

By the way, the same limit is stated in 9i.

There is a chance,that you did not reach this limit; however, as you did not post the exact error message (with whole stack and line numbers), it is hard to say.
Re: Problem in Insert all in Oracle10g [message #356612 is a reply to message #356518] Fri, 31 October 2008 08:54 Go to previous message
sspn2010
Messages: 162
Registered: October 2008
Senior Member
Hi,

Accotding to Oracle documentation a single multitable insert statement can contain upto 127 WHEN Clauses. But my statement is having more than 127 WHEN clauses, so i'm getting TOO MANY VALUES error.

Thanks
Srinath
Previous Topic: Select tables with more than one row
Next Topic: Dynamic Cursor in Procedure
Goto Forum:
  


Current Time: Thu Dec 08 21:57:22 CST 2016

Total time taken to generate the page: 0.08401 seconds