ORA-06502: PL/SQL: Numeric or value error

[CassidyHunt]

I am pulling data from a blob and for 90% of the records this works fine. However for the other 10% I get the ORA-06502 error. I know I could handle this with an exception but it kicks me out of my loop if I do that. I was wondering if there is a better way to handle blobs then what I am using.

declare 
  -- Local variables here
  i integer;
  test varchar2(4000);
  
  procedure prt(x varchar2) is
  begin
       dbms_output.put_line(x);
  end;
begin
  -- Test statements here
  for a in (Select o.workorder_sub_id, o.sequence_no, o.resource_id, b.bits
from operation o, operation_binary b
where o.workorder_type = 'W'
and o.workorder_base_id = 'Q05458'
and o.workorder_lot_id = '1'
and o.workorder_split_id = '0'
and o.workorder_sub_id = 0
and o.sequence_no = 40
and b.workorder_type = o.workorder_type
and b.workorder_base_id = o.workorder_base_id
and b.workorder_lot_id = o.workorder_lot_id
and b.workorder_split_id = o.workorder_split_id
and b.workorder_sub_id = o.workorder_sub_id
and b.sequence_no = o.sequence_no
  ) loop
   
  
  test := utl_raw.cast_to_varchar2(a.bits);
  
  if lengthb(test) > 4000 then 
     test := 'No dice';
  end if;
  
  prt(a.sequence_no||'     '||test);
  end loop;
end;

Thanks

Cassidy

 

[SantaMufasa]

Actually, Cassidy, you can have the best of both worlds by coding your block(s) in such a way that the exception handler is inside the loop:

declare
...
    bad_value	exception;
    pragma exception_init(bad_value,-6502);
begin
...
    for r in (select ...) loop
        begin
            ...
            <statement that throws "numeric or value error";
        exception
            when bad_value then
                <do what you want/need to handle error>;
        end;
    end loop;
...
end;
/

With the above coding strategy, you handle the errors and you stay inside the loop;

If you have questions, let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CassidyHunt]

What if you get the error during the select process?

My code gets the same error regardless of what I do after the word in the loop. This is where I am confused. I can pull the data using just the select but when I make it part of a PL/SQL application in a for loop like this I get that error. I tried handling it like that after the loop statement but the error still resides and it points to the select portion of the loop.

Does this make sense?

Thanks

Cassidy

 

[lewisp]

A quick question. Are both of the columns workorder_sub_id and sequence_no number columns?

 

[CassidyHunt]

Yes they are both number columns. Although I tried a string and get the same exact response.

 

[CassidyHunt]

Figured out one my issues to what is happening. I was told that only text was stored in the fields. After looking a little closer I found they store files in these fields too.

Is there a way to test the blob to find out which it is?

 

[CassidyHunt]

One last thing is that if the text has more than a certain number of characters it seems to error out as well. I do not know that number but I shorten some of the text in the fields and it pulls fine.

Thanks for your help so far.

Cassidy

 

[CassidyHunt]

Solved my problem.

I made an assumption that size of the binary field exceeds 10,000 then its a file. From here I was able to change 100% of the fields to varchar2. The next problem I encountered was that DBMS_OUTPUT.PUT_LINE had a character limitation for the number of characters it would actually output to the screen. So I used the substr function to limit my output to 100 characters.

What really bugs me about how I was able to achieve this is I had to make an assumption that in no point in time would there ever be a text field exceed a binary length of 10,000. It works for now so I am not complaining.

Thanks everyone for your help.

Cassidy