swarna plz give me replay any bady,In this code runtime error is coming can you find

[swarnavplsql]

In this code ,i think in insert statement select quary returning more than one value, that is only error .....plz tell me any body

[CODE=oracle]cursor c1 is select a.f_credit_insu r_flag,a.v_chn_ fin_label,c.fic _mis_date,
c.v_label_code, a.fic_mis_date, a.f_recorse_fla g,a.f_undertaki ng_flag,
a.f_fldg_flag from stg_channel_fin ance a,stg_guarantee b,
stg_loan_contra cts c;

Begin
open c1;
loop
Fetch c1 into af_credit_insur _flag,av_chn_fi n_label,cfic_mi s_date,
cv_label_code,a fic_mis_date,af _recorse_flag,a f_undertaking_f lag,af_fldg_fla g;
Exit when c1%notfound;

if(af_credit_in sur_flag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,av_chn_fin_lab el,afic_mis_dat e);
End if;

if(af_recorse_f lag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'RC') ,
afic_mis_date);
End if;

if(af_undertaki ng_flag='Y') then
insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'VG') ,
afic_mis_date);
End if;

if(af_fldg_flag ='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'FG') ,
afic_mis_date);

End if;

End loop;
close c1;

Return 1;

exception
when others then
rollback;
return'error: '||sqlerrm;

End;[/CODE]

[debasisdas]

I think somthing is missing in the code.

Are you trying to write a function.

again it is impossible for me to run the code and check for the errors with out the table structure.

please post all the details with table structure and the exact error message that u are getting.

[holdingbe]

In this code ,i think in insert statement select quary returning more than one value, that is only error .....plz tell me any body

[CODE=oracle]cursor c1 is select a.f_credit_insu r_flag,a.v_chn_ fin_label,c.fic _mis_date,
c.v_label_code, a.fic_mis_date, a.f_recorse_fla g,a.f_undertaki ng_flag,
a.f_fldg_flag from stg_channel_fin ance a,stg_guarantee b,
stg_loan_contra cts c;

Begin
open c1;
loop
Fetch c1 into af_credit_insur _flag,av_chn_fi n_label,cfic_mi s_date,
cv_label_code,a fic_mis_date,af _recorse_flag,a f_undertaking_f lag,af_fldg_fla g;
Exit when c1%notfound;

if(af_credit_in sur_flag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,av_chn_fin_lab el,afic_mis_dat e);
End if;

if(af_recorse_f lag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'RC') ,
afic_mis_date);
End if;

if(af_undertaki ng_flag='Y') then
insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'VG') ,
afic_mis_date);
End if;

if(af_fldg_flag ='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'FG') ,
afic_mis_date);

End if;

End loop;
close c1;

Return 1;

exception
when others then
rollback;
return'error: '||sqlerrm;

End;[/CODE]

Hi,
In cursor you did'nt wrirte any joins in cursor declartions.The oracle server
gives cartision product...In one row can be repeated more than one. so you got errors please write joins conditions based on the your requirements. please
avoid the rollback,commit in pl/sql procedure.

[nramrits]

In this code ,i think in insert statement select quary returning more than one value, that is only error .....plz tell me any body

[CODE=oracle]cursor c1 is select a.f_credit_insu r_flag,a.v_chn_ fin_label,c.fic _mis_date,
c.v_label_code, a.fic_mis_date, a.f_recorse_fla g,a.f_undertaki ng_flag,
a.f_fldg_flag from stg_channel_fin ance a,stg_guarantee b,
stg_loan_contra cts c;

Begin
open c1;
loop
Fetch c1 into af_credit_insur _flag,av_chn_fi n_label,cfic_mi s_date,
cv_label_code,a fic_mis_date,af _recorse_flag,a f_undertaking_f lag,af_fldg_fla g;
Exit when c1%notfound;

if(af_credit_in sur_flag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,av_chn_fin_lab el,afic_mis_dat e);
End if;

if(af_recorse_f lag='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'RC') ,
afic_mis_date);
End if;

if(af_undertaki ng_flag='Y') then
insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,
fic_mis_date)va lues((select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'VG') ,
afic_mis_date);
End if;

if(af_fldg_flag ='Y') then

insert into stg_exp_mitigan t_mapping(v_exp osure_id,v_miti gant_code,fic_m is_date)values( (select v_loan_acct_no from stg_loan_contra cts c,stg_channel_f inance a
where a.v_chn_fin_lab el=c.v_label_co de and a.fic_mis_date= c.fic_mis_date) ,CONCAT(av_chn_ fin_label,'FG') ,
afic_mis_date);

End if;

End loop;
close c1;

Return 1;

exception
when others then
rollback;
return'error: '||sqlerrm;

End;[/CODE]

PLEASE CHECK THE INSERT STATEMENT THE NO OF COLUMNS ARE NOT SAME IN SELECT STATEMENT AND AVOID VALUES KEYWORD
EX
INSERT INTO TABLE_NAME(C1,C 2,C3) SELECT C1,C2,C3 TABLE_NAME;

[nramrits]

Please Check The Insert Statement The No Of Columns Are Not Same In Select Statement And Avoid Values Keyword
Ex
Insert Into Table_name(c1,c 2,c3) Select C1,c2,c3 Table_name;

[amitpatel66]

U need to have same no of columns in both insert and select statement.

And Please post the exact error message that Oracle shows when u try executing a piece of code. Please do not make a guess or assumption.

Thanks