• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1495
  • Last Modified:

ORA-6502 ERROR

Hello!  
I have the following code in a trigger called Post-Query.

-- Populate display_bslip_changed
if :view_discrepancies.bslip_changed is null then
   :view_discrepancies.display_bslip_changed := 'NA';
--   :view_discrepancies.bslip_changed := '0';
elsif :view_discrepancies.bslip_changed = '0' then
   :view_discrepancies.display_bslip_changed := 'No';
elsif :view_discrepancies.bslip_changed = '1' then
   :view_discrepancies.display_bslip_changed := 'Yes';
elsif :view_discrepancies.bslip_changed = '2' then
   :view_discrepancies.display_bslip_changed := 'NA';
end if;


-- Display the current Sort Order
:display.sort_order := 'Ordered By '||Get_Block_Property( 'VIEW_DISCREPANCIES', order_by);
-- Parse :display.sort_order looking for 'Ordered By GET_'.  If found
-- remove the 'GET_'.
if substr(:display.sort_order,1,15) = 'Ordered By GET_' then
   :display.sort_order := 'Ordered By '||substr(substr(:display.sort_order,16),1,(instr(substr(:display.sort_order,16),'(') -1))||
                           ','||substr(:display.sort_order,instr(:display.sort_order,')') +2);
end if ;

-- Reset Default-Where to null after each query
set_block_property('view_discrepancies',default_where,' ');


-- Populate PC Code
:view_discrepancies.pc_code := get_pccode(:view_discrepancies.project,:view_discrepancies.bundle);

-- Populate ONC initails
:view_discrepancies.onc := get_onc(:view_discrepancies.project,:view_discrepancies.bundle);

-- Depending on which view selected, update the ONC initials
onc_mteam_pairs;

declare
   x number(2):=0;
Begin

-- Populate display_discr_type
for i in 1..7 loop
   if substr(:view_discrepancies.discr_type,i,1) = 'A' then
           x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Bad Address;';
   elsif substr(:view_discrepancies.discr_type,i,1) = 'B' then
           x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Invalid Box;';
-- highlighting the row in red when bad box
      DISPLAY_ITEM('view_discrepancies.display_discr_type','highlight');
      DISPLAY_ITEM('view_discrepancies.onc','highlight');
      DISPLAY_ITEM('view_discrepancies.project','highlight');
      DISPLAY_ITEM('view_discrepancies.week','highlight');
      DISPLAY_ITEM('view_discrepancies.pc_code','highlight');
      DISPLAY_ITEM('view_discrepancies.bundle','highlight');
      DISPLAY_ITEM('view_discrepancies.dropper','highlight');
      DISPLAY_ITEM('view_discrepancies.binvest_flag','highlight');
      DISPLAY_ITEM('view_discrepancies.display_bslip_changed','highlight');
      DISPLAY_ITEM('view_discrepancies.box_id','highlight');
      DISPLAY_ITEM('view_discrepancies.box_time_ind','highlight');
      DISPLAY_ITEM('view_discrepancies.display_status','highlight');
      DISPLAY_ITEM('view_discrepancies.display_result','highlight');
   elsif substr(:view_discrepancies.discr_type,i,1) = 'L' then
         x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Bad LPU;';
       
   elsif substr(:view_discrepancies.discr_type,i,1) = 'C' then
         x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Critical LPU;';
-- highlighting the row in red when critical lpu
      DISPLAY_ITEM('view_discrepancies.display_discr_type','highlight');
      DISPLAY_ITEM('view_discrepancies.onc','highlight');
      DISPLAY_ITEM('view_discrepancies.project','highlight');
      DISPLAY_ITEM('view_discrepancies.week','highlight');
      DISPLAY_ITEM('view_discrepancies.pc_code','highlight');
      DISPLAY_ITEM('view_discrepancies.bundle','highlight');
      DISPLAY_ITEM('view_discrepancies.dropper','highlight');
      DISPLAY_ITEM('view_discrepancies.binvest_flag','highlight');
      DISPLAY_ITEM('view_discrepancies.display_bslip_changed','highlight');
      DISPLAY_ITEM('view_discrepancies.box_id','highlight');
      DISPLAY_ITEM('view_discrepancies.box_time_ind','highlight');
      DISPLAY_ITEM('view_discrepancies.display_status','highlight');
      DISPLAY_ITEM('view_discrepancies.display_result','highlight');
   elsif substr(:view_discrepancies.discr_type,i,1) = 'M' then
         x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Bad Box ID;';
   elsif substr(:view_discrepancies.discr_type,i,1) = '#' then
         x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' No BOX ID;';
   elsif substr(:view_discrepancies.discr_type,i,1) = 'O' then
         x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Bad Closing Time;';
   elsif substr(:view_discrepancies.discr_type,i,1) = 'F' then
          x:=x+1;
      :view_discrepancies.display_discr_type := :view_discrepancies.display_discr_type||' Fcast Box Missing;';
   end if;
   if i = 6 then
      :view_discrepancies.display_discr_type := substr(:view_discrepancies.display_discr_type,2);
   end if;
   if x>1 then
         DISPLAY_ITEM('view_discrepancies.display_discr_type','highlight');
      DISPLAY_ITEM('view_discrepancies.onc','highlight');
      DISPLAY_ITEM('view_discrepancies.project','highlight');
      DISPLAY_ITEM('view_discrepancies.week','highlight');
      DISPLAY_ITEM('view_discrepancies.pc_code','highlight');
      DISPLAY_ITEM('view_discrepancies.bundle','highlight');
      DISPLAY_ITEM('view_discrepancies.dropper','highlight');
      DISPLAY_ITEM('view_discrepancies.binvest_flag','highlight');
      DISPLAY_ITEM('view_discrepancies.display_bslip_changed','highlight');
      DISPLAY_ITEM('view_discrepancies.box_id','highlight');
      DISPLAY_ITEM('view_discrepancies.box_time_ind','highlight');
      DISPLAY_ITEM('view_discrepancies.display_status','highlight');
      DISPLAY_ITEM('view_discrepancies.display_result','highlight');
         end if;
end loop;

-- Populate display_status
if :view_discrepancies.bundle is not null then
if :view_discrepancies.status is null then
   if substr(:view_discrepancies.discr_type,1,1) = 'B' then
      :view_discrepancies.display_status := 'M-Team Ready';
--      :view_discrepancies.status := '2';
   else
      :view_discrepancies.display_status := 'A-Team Ready';
--      :view_discrepancies.status := '1';
   end if;
elsif :view_discrepancies.status = '1' then
   :view_discrepancies.display_status := 'A-Team Ready';
elsif :view_discrepancies.status = '2' then
   :view_discrepancies.display_status := 'M-Team Ready';
elsif :view_discrepancies.status = '3' then
   :view_discrepancies.display_status := 'Workroom Ready';
elsif :view_discrepancies.status = '4' then
   :view_discrepancies.display_status := 'Complete';
elsif :view_discrepancies.status = '5' then
   :view_discrepancies.display_status := 'M-Team In Progress';
end if;
end if;

--Populate display_result
if :view_discrepancies.result is not null then
   select substr(description,1,30)
   into :view_discrepancies.display_result
   from ttms_codes
   where type = 'CBMS DISCREPANCY RESULT' and
         code = :view_discrepancies.result;
end if;


-- Populate dropper_id
begin
   select a.dropper
   into :view_discrepancies.drop_id
   from induction_info a
   where a.bundle = to_number(:view_discrepancies.bundle);
exception
 when no_data_found then
  null;
end;

END;

The thing works fine except for when there are more than 3 discrepancy types.  When this happens, I get a POST-QUERY raised unhandled exception ORA-06502.  Is there something in the trigger that needs to be changed, or is there someother place in the form I should be looking to update.
Thanks
I would like to have this completed by COB today.
0
christie_holtz
Asked:
christie_holtz
1 Solution
 
Helena Markováprogrammer-analystCommented:
Maybe the problem is caused by an item :view_discrepancies.bundle. Is it always a number in a string ?

-- Populate dropper_id
begin
   select a.dropper
   into :view_discrepancies.drop_id
   from induction_info a
   where a.bundle = to_number(:view_discrepancies.bundle);
exception
 when no_data_found then
  null;
end;
0
 
konektorCommented:
what type is ":view_discrepancies.bundle". the error occurs when u r converting strings variable to number in cases, when variable does'n hold number.

ORA-06502 PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now