Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect Error
Bulk Collect Error [message #188216] Thu, 17 August 2006 08:16 Go to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I got this pls/sql procedure and after run I get ORA error on line (=> mark): PL/SQL numeric or value error

declare
TYPE t_mape IS TABLE OF table_site_part%ROWTYPE INDEX BY LS_INTEGER;
l_list t_mape;  
l_list2 t_mape; 

begin

SELECT * BULK COLLECT INTO l_list FROM table_site_part WHERE status=123;
  FOR i IN l_list.FIRST..l_list.LAST
   LOOP
    select sth into v_sth from table_site
    where id=l_list(i).service;

   for j in (select objid,type 
             from table_site 
             where id = v_sth) loop
  
   SELECT * BULK COLLECT INTO l_list2 FROM table_site_part  
   WHERE site =j.objid; 
=>   for rec_c_su IN l_list2.FIRST..l_list2.LAST
   loop

   do something
   ...





Does anybody know where is the problem?

thanks

Martin

[Updated on: Thu, 17 August 2006 08:17]

Report message to a moderator

Re: Bulk Collect Error [message #188224 is a reply to message #188216] Thu, 17 August 2006 08:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that what is happening is that this
SELECT * BULK COLLECT INTO l_list2 FROM table_site_part  
   WHERE site =j.objid;
is returning no rows for a given value of SITE.
This will creata an empty l_list2, and if you try to itterate round an empty list, you get:

SQL> declare
  2    type ty_tab is table of number index by binary_integer;
  3    t_tab   ty_tab;
  4  begin
  5    for rec in t_tab.first .. t_Tab.last loop
  6      null;
  7    end loop;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
which I suspect looks a lot like your error.
Re: Bulk Collect Error [message #188232 is a reply to message #188216] Thu, 17 August 2006 08:44 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Hi

Your explanation looks a really good,
may be there is no rows for a given value STATE

but how to figute it out?
Re: Bulk Collect Error [message #188234 is a reply to message #188232] Thu, 17 August 2006 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you just want to find out which values are causing the error
SELECT site 
FROM table_site_part  
HAVING count(*) = 0
GROUP BY site;


If you want to trap this in the code, replace
for rec_c_su IN l_list2.FIRST..l_list2.LAST loop
   <do something>
end loop;
with
if l_list2.count > 0 THEN
  for rec_c_su IN l_list2.FIRST..l_list2.LAST loop
    <do something>
  end loop;
end if;
Re: Bulk Collect Error [message #188339 is a reply to message #188216] Fri, 18 August 2006 02:26 Go to previous message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member

the problem is done.

Everything was about what you said.

thanks
Previous Topic: reverse the cross tab in oracle
Next Topic: How can I fin '_'-mark in text-string
Goto Forum:
  


Current Time: Fri Dec 09 06:03:44 CST 2016

Total time taken to generate the page: 0.12396 seconds