Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure error ORA-06512/ORA-06502
Stored procedure error ORA-06512/ORA-06502 [message #277847] Wed, 31 October 2007 19:40 Go to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
I am clueless why I would get this error because I checked the data types and everything but cannot figure out..any help would be much appreciated.

Error message:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SIEBEL.UPDATE_WFS_INBOXTEAM_MEMBERS", line 22
ORA-06512: at line 2


CREATE OR REPLACE PROCEDURE UPDATE_WFS_INBOXTEAM_MEMBERS
as
 prev_inbox varchar2(50);
 prev_owner varchar2(50);
 new_team_members varchar2(2000);
cursor team_cur is select position_id, team as team_mbr
   from WC_WFS_INBOXTEAM_D
   order by position_id;
team_val team_cur%ROWTYPE;
begin
   prev_inbox:= '1-ZZ6MC';
   prev_owner:= '0-1UBC5';
   new_team_members:='';
  open team_cur;
      loop
        fetch team_cur into team_val;
        exit when team_cur%NOTFOUND;
        if prev_inbox = team_val.position_id or new_team_members is null then
            if new_team_members is null then
               new_team_members := team_val.team_mbr;
            else
               new_team_members := new_team_members || '; ' || team_val.team_mbr ;
            end if;
        end if ;
       if prev_inbox <> '1-ZZ6MC' then
        if prev_inbox <> team_val.position_id then
		      update w_position_dx set team = new_team_members
		       where (select d.integration_id from w_position_d d, w_position_dx dx where d.row_wid = dx.row_wid) = prev_inbox;
			     commit;
               new_team_members:= team_val.team_mbr;
         end if;
       end if;
                 prev_inbox := team_val.position_id;
     end loop;
   close team_cur;
if prev_inbox <> '1-ZZ6MC' then
 update w_position_dx set team = new_team_members
       where (select d.integration_id from w_position_d d, w_position_dx dx where d.row_wid = dx.row_wid) = prev_inbox;
			     commit;
         end if;
end;
/
Re: Stored procedure error ORA-06512/ORA-06502 [message #277848 is a reply to message #277847] Wed, 31 October 2007 19:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
OK, I give up.
Which line is #22?

This error is typically 1 of 2 flavors.
1) trying to assign a non-numeric character in some NUMBER field
2) trying to assign a string that is longer than the VARCHAR2 variable

[Updated on: Wed, 31 October 2007 19:48] by Moderator

Report message to a moderator

Re: Stored procedure error ORA-06512/ORA-06502 [message #277850 is a reply to message #277848] Wed, 31 October 2007 19:55 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Line# 22
new_team_members := new_team_members || '; ' || team_val.team_mbr ;
Re: Stored procedure error ORA-06512/ORA-06502 [message #277851 is a reply to message #277847] Wed, 31 October 2007 20:58 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I suspect that new_team_members is expanding & exceeding 2000 characters
Previous Topic: Return rows deleted Using PL/SQL
Next Topic: Mutating Error Problem
Goto Forum:
  


Current Time: Sat Dec 03 01:22:03 CST 2016

Total time taken to generate the page: 0.08294 seconds