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 <[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
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).
