Skip to Main Content
  • Questions
  • Error: ORA-06533: Subscript beyond count

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Neeraj.

Asked: April 07, 2018 - 6:37 pm UTC

Last updated: April 09, 2018 - 3:35 am UTC

Version: Oracle sql developer 11g

Viewed 1000+ times

You Asked

Please help me in editing the below Pl/SQL code:

create or replace
Function user_score_scurve (scurve_in varchar2, inpt IN number)
   RETURN number
IS
   params_number number;
   TYPE type_params IS table OF number;         
   Params type_params;
   output number;
   output1 number;
   output2 number;
   rand number;
BEGIN
   --Set the random number DBMS_RANDOM.VALUE (0, 1)
   rand := 0; 
   -- Bulk collect into collection 
   select value bulk collect into params
     from user_score_scurve_params
    where 1=1
      and scurve = scurve_in;
   -- Calculating the formula: E6+(F6-E6)*1/(1+EXP((G6-(C24-C6)/(D6-C6))/(H6+RAND()/10^6)))
   output1 := params(7) - (inpt - params(2))/(params(3) - params(2)); 
   output2 := params(6) + rand/(power(10,6)); 
   output  := params(4) + (params(5) - params(4))/(1 + exp(output1/output2)); 

return output;
END;


and Connor said...

The calculations you are doing are referring values in param 1 through 7. So if you do not bulk collect 7 or more values into your array, you will get this error. For example, this query will only collect 1 row...so referencing 2 or higher is an issue

SQL> declare
  2    TYPE type_params IS table OF number;
  3    p type_params;
  4  begin
  5    select rownum bulk collect into p
  6    from dual;
  7    p(1) := p(1)+10;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> declare
  2    TYPE type_params IS table OF number;
  3    p type_params;
  4  begin
  5    select rownum bulk collect into p
  6    from dual;
  7    p(2) := p(2) + 10;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 7


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Order in the court

Racer I., April 09, 2018 - 6:59 am UTC

Hi,

Maybe add some deterministc ordering to the fetch to make sure params(7) is really number seven. You are also not using params(1)?

regards,

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library