大家好，请帮助我，在这个问题...我们将数据库从postgresql SQL迁移到oracle但是我收到的错误为“ORA-06533：subscript beyond count”提前致谢 [英] Hi all, please help me , in this issue...we migrate the DB from postgresql SQL to oracle but I am getting the error as "ORA-06533:subscript beyond count" thanks in advance

问题描述

create or replace PROCEDURE refreshrolelevelcount /*refreshrolelevelcount_20090707*/ ("_desk" IN NUMBER, "_level" IN NUMBER, "_origdate" IN DATE)

   as
   v_id  NUMBER(10,0);

   v_nrbl  varchar2(200); --Arinteger;

   v_nRoles  NUMBER(10,0);

   v_n  NUMBER(10,0);

   v_date  DATE;

   v_i  NUMBER(10,0);
   SWV_RetStat  NUMBER(10,0);
   i number(10,0):=0;
   
   type temp_array is varray(200) of varchar2(10);
   v_nrbl_temp  temp_array;
   v_str varchar2(200);
    

begin
-- This procedure was converted on Tue Oct 15 15:06:06 2013 using Ispirer .

v_nrbl_temp := temp_array();

   if ("_origdate" is null)
   then
      begin
         select  CAST(value AS DATE) into v_date from schemavars  WHERE (name = 'currentQuarter') AND ROWNUM <=1;
         EXCEPTION
         WHEN NO_DATA_FOUND THEN
            NULL;
      end;
   else
      v_date := "_origdate";
   end if;



   begin
      select  pd.id, pd.nrolesbylevel into v_id,v_nrbl
      from placedetails pd
      WHERE (pd.place = "_desk" and pd.financialdate = v_date and pd.periodsize = 3 and pd.latest = 1 and pd.active = 1 and pd.deceased = 0) AND ROWNUM <=1;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   end;

   if  SQL%NOTFOUND
   then
      RAISE_APPLICATION_ERROR(-20000,'Couldn''t find place-details record for ' || "_desk");
      return;
   end if; 



   SWV_RetStat := newversion_placedetails(v_id,null,null);



   select  count(id) into v_nRoles
   from role
   where latest = 1 and active = 1 and deceased = 0 and
   desk = "_desk" and rolelevel = "_level" and enddate is null;



   if (v_nrbl is null)
   then
      v_nrbl := '{}';
   end if;

    for cur in (select num  from table(string_to_array(v_nrbl,',')))
    loop
    i:=i+1;
    v_nrbl_temp.extend;
    v_nrbl_temp(i):= cur.num;
    
    end loop;

   i:=v_nrbl_temp.count;

   v_n := coalesce((i+1),0);
 
   for v_i in v_n .. "_level" -1
   loop
      v_nrbl_temp(v_i) := 0;
   

   end loop;

  v_nrbl_temp("_level") := v_nRoles;

   v_str:= '[0:' || to_char(i - 1) || ']={' ;
   
   for v_i in 1 .. i
   loop
   v_str:=v_str || v_nrbl_temp(v_i) ||',' ;
   end loop;

   v_str:= substr(v_str,1,(length(v_str) -1 )) || '}' ; 
    
   update placedetails
   set nrolesbylevel = v_str
   where id = v_id and latest = 1;

   return;

end;

我尝试了什么：



i得到以下错误ORA-06533：超出计数的下标第90行，即for循环内部.. v_nrbl_temp（ v_i）：= 0;





java.sql.SQLException：ORA-06533：超出计数的下标

[16/04/21 19：02：24.412] ORA-06512：atPUBLICDNA_MGRTEST.REFRESHROLELEVELCOUNT，第90行

[16/04/21 19：02：24.412] ORA-06512：在PUBLICDNA_MGRTEST.UPDATEPLACEROLECOUNTS，第204行

[16/04/21 19：02：24.412] ORA-06512：在PUBLICDNA_MGRTEST.ROLESTMTTIU，第90行

[16/04/21 19：02：24.412] ORA-04088：错误执行触发器'PUBLICDNA_MGRTEST.ROLESTMTTIU'期间

[16/04/21 19：02：24.412] ORA-06512：在PUBLICDNA_MGRTEST.STDFUZZYRECALCLATESVALUEFN，第173行

[16/04/21 19：02：24.412] ORA-06512：第1行

[16/04/21 19：02：24.412] ORA-06512：atPUBLICDNA_MGRTEST.PROC_TEST_PRAG，第35行

[16/04/21 19:02 ：24.412] ORA-06512：在PUBLICDNA_MGRTEST.FUZZINESSSTMTTIU，第18行

[16/04/21 19：02：24.412] ORA-04088：执行触发'PUBLICDNA_MGRTEST.FUZZINESSSTMTTIU'时出错

[16/04/21 19：02：24.412] ORA-06512：在PUBLICDNA_MGRTEST.CREATE_FUZZINESS，第16行

[16/04/21 19:02 ：24.412] ORA-06512：在PUBLICDNA_MGRTEST.INSERTFUZZYFACTOR，第200行

[16/04/21 19：02：24.412] ORA-06512：atPUBLICDNA_MGRTEST.INSERTFUZZYFACTORSTR，第43行

[16/04/21 19：02：24.412] ORA-06512：第1行

[16/04/21 19：02：24.412]

What I have tried:

i got the below error "ORA-06533:subscript beyond count" at line no 90 ie inside for loop.. v_nrbl_temp(v_i) := 0;


java.sql.SQLException: ORA-06533: Subscript beyond count
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.REFRESHROLELEVELCOUNT", line 90
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.EXECPLACEJOBS", line 23
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.UPDATEPLACEROLECOUNTS", line 204
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.ROLESTMTTIU", line 90
[16/04/21 19:02:24.412] ORA-04088: error during execution of trigger 'PUBLICDNA_MGRTEST.ROLESTMTTIU'
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.STDFUZZYRECALCLATESVALUEFN", line 173
[16/04/21 19:02:24.412] ORA-06512: at line 1
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.PROC_TEST_PRAG", line 35
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.FUZZINESSSTMTTIU", line 18
[16/04/21 19:02:24.412] ORA-04088: error during execution of trigger 'PUBLICDNA_MGRTEST.FUZZINESSSTMTTIU'
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.CREATE_FUZZINESS", line 16
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.INSERTFUZZYFACTOR", line 200
[16/04/21 19:02:24.412] ORA-06512: at "PUBLICDNA_MGRTEST.INSERTFUZZYFACTORSTR", line 43
[16/04/21 19:02:24.412] ORA-06512: at line 1
[16/04/21 19:02:24.412]

推荐答案

所以问题发生在那个小循环中：

So the problem happens in that small loop:

for v_i in v_n .. "_level" -1
loop
   v_nrbl_temp(v_i) := 0;
end loop;

这意味着您的索引 v_i 太大（或太小）。该数组包含多少项？ v_n 和 _level 的价值是多少？正确调整它们。

Which means that your index v_i is too big (or too small). How many items does that array contain? What's the value of v_n and "_level"? Adjust them correctly.

这篇关于大家好，请帮助我，在这个问题...我们将数据库从postgresql SQL迁移到oracle但是我收到的错误为“ORA-06533：subscript beyond count”提前致谢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！