Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

for-in-loop produces "ORA-06502: PL/SQL: numeric or value error"

WestDraytonMar 10 2010 — edited May 16 2013
Why i'm getting error "ORA-06502: PL/SQL: numeric or value error" in below code.
create table bc (
  a number, 
  b varchar2(10)
);


declare
  type t_bc_a is table of bc.a%type;
  type t_bc_b is table of bc.b%type;
  l_bc_a t_bc_a;
  l_bc_b t_bc_b;
begin
  dbms_output.put_line('1');
  select a, b bulk collect into l_bc_a, l_bc_b from bc;
   dbms_output.put_line('2');
   if l_bc_a is null then
     dbms_output.put_line('2.1'); 
   else
     dbms_output.put_line('2.2, l_bc_a.count=' || l_bc_a.count); 
     
   end if;
  for i in l_bc_a.first .. l_bc_a.last loop
   dbms_output.put_line('3');
    dbms_output.put_line(l_bc_a(i) || ', ' || l_bc_b(i));
  end loop;
end;

/*
1
2
2.2, l_bc_a.count=0
ORA-06502: PL/SQL: numeric or value error
*/
Table "bc" is empty, and i expect for-loop to never do any cycle/step, but i get error. Why the error is there.
This post has been answered by ttt on Mar 10 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 13 2013
Added on Mar 10 2010
6 comments
7,906 views