ORA-00926: missing VALUES keyword

[loveoracle]

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.

[davey23uk]

youa re missing the values keyword in your insert statement

[loveoracle]

Hey Friend,

But I am using a subquery after insrt statement.
then why it gives me an error.

[davey23uk]

because you are missing the values keyword

[ebrian]

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

What is RBI.CBS_ARTICLE5_WORK_INR anyway?

[davey23uk]

that would be him renmaing the procedure in the post when its really called

CBS_ARTICLE5_WORK_INR

[WilliamR]

Perhaps it depends on the value supplied in the mcondition parameter.

Capture the generated string, and test that to see what is wrong with it.

btw this procedure looks like the most inefficient approach possible, but without knowing anything about the requirements I can't suggest an alternative. I would find the idea of allowing the text of an SQL condition to be passed in rather worrying, if that's what it is.