DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-00926: missing VALUES keyword

  1. #1
    Join Date
    May 2006
    Posts
    2

    ORA-00926: missing VALUES keyword

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    youa re missing the values keyword in your insert statement

  3. #3
    Join Date
    May 2006
    Posts
    2
    Hey Friend,

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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    because you are missing the values keyword

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    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?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    that would be him renmaing the procedure in the post when its really called

    CBS_ARTICLE5_WORK_INR

  7. #7
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.
    Last edited by WilliamR; 05-12-2006 at 02:19 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width