to_number question

[sanrak]

Hi All

Can anybody explain this

Oracle Version 8.0.5 Windows NT and HP UNIX

SQLPLUS> select to_number('54.12','99.99') from dual;
54.12
SQLPLUS>c/54.12/,
SQLPLUS>;
select to_number(',','99.99') from dual;
SQLPLUS> /
5.12


Any idea about this ?????

TIA
Santosh

[pwoneill]

Hrm, did it on 8i and 8.0.5 and got the same result.

<font face="courier">
SQL> select to_number('54.12', '99.99') from dual;

TO_NUMBER('54.12','99.99')
--------------------------
54.12

SQL> c/54.12/,
1* select to_number(',', '99.99') from dual
SQL> /

TO_NUMBER(',','99.99')
----------------------
0

SQL>
</font>

[sambavan]

I agree with pwoneill. It is the same when i tried on 8.1.6 and 8.0.4 on solaris and aix

SQL> select to_number('54.12','99.99') from dual;

TO_NUMBER('54.12','99.99')
==========================
54.12

SQL> c/54.12/,
1* select to_number(',','99.99') from dual
SQL> /

TO_NUMBER(',','99.99')
======================
0

SQL>


You might require some patch.

Sam

[sanrak]

Thanks for the answers

its happening only in 8.0.5 , other versions are fine

santosh

[sanrak]

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production

SQL> select to_number('54.12','99.99') from dual;

TO_NUMBER('54.12','99.99')
--------------------------
54.12

SQL> c/54.12/,
1* select to_number(',','99.99') from dual
SQL> /

TO_NUMBER(',','99.99')
----------------------
4.12

If its a bug , i couldn't find anything in metalink?
any idea about the bug number???

Thanks
Santosh

[pwoneill]

Was it on HP, NT, or both? Maybe it's platform specific, I'm on Solaris.

[sanrak]

On HP and NT both.

Thanks,
Santosh

[sanrak]

Hi pwoneill

Can you please send me the output of the V$version view from 8.0.5 database

TIA
Santosh

[pwoneill]

I just recently applied the 8.0.5.2.1 patch so mine is

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production
PL/SQL Release 8.0.5.2.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for Solaris: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production

SQL>

The readme.txt for the patch is at [url]ftp://192.86.152.21/pub/patches/patchsets/610/80521/README.txt[/url] . It's actually in HTML format but they saved it as a .txt, so for easier reading you might have to save it and open it locally in a browser.

The only thing regarding to_number in the patch set is "PLSQL assignment using TO_NUMBER dumps when ORA-1722 expected"

[sanrak]

Thanks

Let me try the 8.0.5.2.1 Patchset


Santosh