getting ORA-06550 error

Question

Declare
  type v_year_arr is varray(100) of emps%rowtype;
  v_deptid emps.department_id%type :=90;
  v_rem1 number(5,2);
  v_rem2 number(5,2);  
  v_rem3 number(5,2);
  v_year v_year_arr;
Begin
   select * bulk collect into v_year from emps where department_id = v_deptid;
   v_rem1 := MOD(v_year.yof,4);
   v_rem2 := MOD(v_year.yof,100);
   v_rem3 := MOD(v_year.yof,400);
   for i..v_year loop
      If (v_rem1=0 and v_rem2=0 and v_rem3=0)
      then
        DBMS_OUTPUT.PUT_LINE('Leap year :'||v_year.yof);
      else
        DBMS_OUTPUT.PUT_LINE('Not a Leap year :'||v_year.yof)
      End if;
   end loop;
end;

Please edit your question and include additional information such as the exact text of the error message, an indication of which line is being flagged, etc. Thanks. — Bob Jarvis - Слава Україні, Aug 08 '13 at 12:58

score 2 · Answer 1 · answered Aug 08 '13 at 13:10

I think you'd be better off writing this using a cursor FOR loop, as in:

Declare
  v_deptid emps.department_id%type :=90;
  v_rem1 number(5,2);
  v_rem2 number(5,2);  
  v_rem3 number(5,2);
Begin
  FOR aRow IN (select * from emps where department_id = v_deptid)
  LOOP
    v_rem1 := MOD(aRow.yof,4);
    v_rem2 := MOD(aRow.yof,100);
    v_rem3 := MOD(aRow.yof,400);

    If (v_rem1=0 and v_rem2<>0) or (v_rem2=0 and v_rem3=0) then
      DBMS_OUTPUT.PUT_LINE('Leap year :'||aRow.yof);
    else
      DBMS_OUTPUT.PUT_LINE('Not a Leap year :'||aRow.yof)
    End if;
  end loop;
end;

This eliminates the potential problem of having more than 100 rows returned by the SELECT...BULK COLLECT INTO... statement. I also took the liberty of correcting your leap year determination.

Share and enjoy.

getting ORA-06550 error

1 Answers1