RE: To_Number

Thanks for the email!! I did not know about Dump so I learned something new. The record I thought I had a problem with was ok and that is why I did not see anything amiss. However the record after this one was $20041-94. Corrected that and got $145.34 EA. I just told the developer to handle the 1722 error and let someone else deal with how to correct the data. It's ridiculous!

Laura

-----Original Message-----
Sent: Thursday, December 05, 2002 8:29 PM To: Multiple recipients of list ORACLE-L

Did you check it with dump() ?

select dump(unit_cost,16) from tablewhatever;

for Hex. Check for unprintables that way. Try inserting the bad values into a look-aside table for later analysis i.e.

create table look_aside (rownum rowid, bad_val varchar2(20), dump_val varchar2(200));

declare
  bad_num exception;
  numval number(10,2);
  pragma exception_init(bad_num,-1722);
  Cursor C1 is select rowid,unit_cost from yourtable; begin
  for x in C1
  loop
    declare -- inner block will allow exception trap     begin

• trim and change o's to zeroes numval := to_number(rtrim(replace(x.unit_cost,'O','0'))); exception when bad_num then insert into look_aside values (x.rowid,x.unit_cost, substr(dump(unit_cost,16),1,200)); end; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); end; /

You can use the rowids in the look-aside table to zap the bad values later.

HTH Jeff Herrick

On Thu, 5 Dec 2002, Burton, Laura L. wrote:

> Since we don't have that many 3rd party software packages I did make the
> mistake of asking 'Why??' and received 'Because!' so I too am trying to
jump
> in and 'fix it'. As I responded to another email earlier, the RTrim
worked
> because there were spaces after the amount which was causing the problem.
> The only problem now is I have one record (so far) that has a unit cost
that
> looks like any other unit cost, yet I receive 'invalid number' for it.
The
> only thing I can figure is that there must be an unprintable character in
> the field that I cannot see and rtrim is not deleting since it isn't a
> space.
>
> Laura
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Herrick
  INET: jherrick_at_igs.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Burton, Laura L.
  INET: BurtonL_at_prismplus.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).