RE: To_Number

It's obvious they are using Oracle as a glorified fileing system.

JD Edwards does the same thing. They create tables with columns with things like Number(38). they store the number with no decimal points. the "middle layer" has the definition of the column. so all applications have to call the middle layer to get the data and convert it to the correct decimal places before it can be acted upon by the application.

I think their theory was to keep the JD Edwards system totally RDBMS neutral - they could switch out any backend that they wanted to.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Friday, December 06, 2002 10:34 AM To: Multiple recipients of list ORACLE-L

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 <ORACLE-L_at_fatcity.com>       
                    <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: 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: Thomas Day
  INET: tday6_at_csc.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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).