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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
