That's probably why it's in a VARCHAR.
"Correct the data"? What strange ideas you mortals have.
"Burton,
Laura L." To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<BurtonL cc:
@prismplus.co Subject: RE: To_Number
m>
Sent by: root
12/06/2002
09:44 AM
Please
respond to
ORACLE-L
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
