ORA-00926: missing VALUES keyword
:rolleyes: Dear All,
Here Is my procedure,
[pre]
CREATE OR REPLACE PROCEDURE TEST (mstring in varchar2, mcondition in varchar2)
IS
-- variable declaration
vstring VARCHAR2(1000) := mstring;
vnum VARCHAR2(10);
vcondition VARCHAR2(4000) := mcondition;
VFOURTH_PART VARCHAR2(4000) := ''' getvalueinusd(Abs(ibs_work_bankdata.bd_fc_bal) +''||
'' Abs(ibs_work_bankdata.bd_fc_int),''||
'' (select ibs_currencymaster.cur_id''||
'' from ibs_currencymaster ''||
'' where ibs_work_bankdata.bd_curcd = ''||
'' ibs_currencymaster.cur_code),''||
'' ibs_work_bankdata.bd_yrqtr)''||
'' else 0 end))''';
CURSOR c1 IS
SELECT TO_CHAR(a.COLUMN_VALUE) num
FROM TABLE(CAST(split(vstring,',')as split_tbl)) a;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO vnum;
EXIT WHEN C1%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO ibs_cbs_temp '||
' SELECT a.YearQtr,a.cgs_name Groupname,a.Code,a.Name1,'||
' Round((nvl(bSum1, 0) + nvl(cSum1, 0) + nvl(dSum1, 0) +'||
' nvl(eSum1, 0) + nvl(fSum1, 0)) / 1000000) as TOTAL,'||
' Round((nvl(bSum1, 0)) / 1000000) as MaturityCode1,'||
' Round((nvl(cSum1, 0)) / 1000000) as MaturityCode2,'||
' Round((nvl(dSum1, 0)) / 1000000) as MaturityCode3,'||
' Round((nvl(eSum1, 0)) / 1000000) as MaturityCode4,'||
' Round((nvl(fSum1, 0)) / 1000000) as MaturityCode5,'||
' Round((nvl(gBank, 0)) / 1000000) as sBank,'||
' Round((nvl(hBank, 0)) / 1000000) as SPrivate,'||
' Round((nvl(iBank, 0)) / 1000000) as SPublic,'||
' Round((nvl(i1Bank, 0)) / 1000000) as SUnallocated'||
' FROM '||' ( '||
' SELECT DISTINCT cg.cgs_name,aq.CTY_Code as Code,'||
' aq.CTY_Name as Name1,ibs_work_bankdata.bd_yrqtr as yearqtr'||
' FROM '||
' Ibs_Countrygroups cg '||
' INNER JOIN IBS_CountryMaster aq ON cg.cgs_id=aq.cty_ctygrpid'||
' AND cg.cgs_active=1 AND aq.cty_active=1'||
' INNER JOIN ibs_work_bankdata ON aq.CTY_Code =ibs_work_bankdata.bd_councd'||
' INNER JOIN ibs_bankmaster bk ON ibs_work_bankdata.bd_bkcode = bk.bnk_code'||
' INNER JOIN ibs_countrymaster cm ON bk.bnk_countryofinc = cm.cty_id'||
' INNER JOIN IBS_ALCategory ON ibs_work_bankdata.bd_alcd = IBS_ALCategory.Alc_Code'||
' WHERE ibs_work_bankdata.bd_yrqtr '||
'='||
vnum||
' GROUP BY cg.cgs_name,aq.CTY_Name,aq.CTY_Code,ibs_work_bankdata.bd_yrqtr'||
' )'||' a'||
' left join'||
' (select sum(('||
' case when ibs_work_bankdata.bd_matcd = 1'||' AND '||VCONDITION||' then'||
' getvalueinusd(Abs(ibs_work_bankdata.bd_fc_bal) +'||
' Abs(ibs_work_bankdata.bd_fc_int),'||
' (select ibs_currencymaster.cur_id'||
' from ibs_currencymaster '||
' where ibs_work_bankdata.bd_curcd = '||
' ibs_currencymaster.cur_code),'||
' ibs_work_bankdata.bd_yrqtr)'||
' else 0 end))'||
' as bSum1,'||
' sum((case when ibs_work_bankdata.bd_matcd = 2 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as cSum1,'||
' sum((case when ibs_work_bankdata.bd_matcd = 3 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as dSum1,'||
' sum((case when ibs_work_bankdata.bd_matcd = 4 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as eSum1,'||
' sum((case when ibs_work_bankdata.bd_matcd = 5 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as fSum1,'||
' sum((case when IBS_Sector.SEC_Parent = 0 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as gBank,'||
' sum((case when IBS_Sector.SEC_Parent = 1 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as hBank,'||
' sum((case when IBS_Sector.SEC_Parent = 2 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as iBank,'||
' sum((case when IBS_Sector.SEC_Parent = 3 '||' AND '||VCONDITION||
' then '||VFOURTH_PART||' as i1Bank,'||
' ibs_work_bankdata.bd_councd as Cty_Id'||
' FROM IBS_CountryMaster aq '||
' INNER JOIN ibs_work_bankdata ON aq.CTY_Code = ibs_work_bankdata.bd_councd'||
' inner join ibs_bankmaster bk on ibs_work_bankdata.bd_bkcode = bk.bnk_code'||
' inner join ibs_countrymaster cm on bk.bnk_countryofinc = cm.cty_id'||
' INNER JOIN IBS_Sector ON ibs_work_bankdata.bd_sectcd = IBS_Sector.Sec_Code'||
' INNER JOIN IBS_ALCategory ON ibs_work_bankdata.bd_alcd = IBS_ALCategory.Alc_Code'||
' INNER JOIN IBS_ALTypes ON ibs_work_bankdata.bd_typecd = IBS_ALTypes.Act_Code'||
' INNER JOIN IBS_MaturityCodeMaster ON ibs_work_bankdata.bd_matcd = IBS_MaturityCodeMaster.MCM_Code'||
' where ibs_work_bankdata.bd_yrqtr '||' = '|| vnum ||
' group by ibs_work_bankdata.bd_councd) l on a.Code = l.CTY_Id'||
' order by total desc';
END LOOP;
CLOSE C1;
COMMIT;
END;
[/pre]
This procedure is excuted successfully from SQL PLUS window.
But from application or Pl/SQL DEVELOPER test window it gives following error;
ORA-06512: at line 1
ORA-00926: missing VALUES keyword
ORA-06512: at "RBI.CBS_ARTICLE5_WORK_INR", line 16
ORA-06512: at line 3
Please some one help me.
Thanks in advance,
Prathamesh.
