Help with to_number

Hi,

I am trying to use to_number to make sure the number I am passing to Oracle is of the correct precision.

The problem I'm having is that Oracle seems to view a value of 4.50 as 45.00. My SQL statement is:

INSERT INTO
ETECDBA.IVR_COMMANDTABLE
(
RECORDKEY,
AGENCYDESIGNATOR,
PAYMENTAMOUNT,
CREDITORDEBITCARDTYPE,
CREDITORDEBITCARDNUMBER,
CREDITORDEBITCARDEXPIRYDATE,
CREDITORDEBITCARDAUTHNUM,
VENDORID,
VENDORTRANSACTIONNUMBER,
COMMANDNUMBER
) VALUES (
$recordKey,
'$agency',
to_number ($amountDue, '999.99'),
'$strCCType',
'$strChoppedCCNum',
'$strExpiryDate',
'$authNum',
'$vendorID',
$refNum,
$command)

.. where $amountDue is 4.50 .

Any reason why Oracle would see it as 45.00?

What is the datatype of PAYMENTAMOUNT column?

Number(15,2)

This looks like a command line insert - maybe from a here doc -- in unix.
if you echo $amountDue what EXACTLY does it show?

What's wrong with using round() to ensure the proper number of decimals?

Thomas

So upon further testing it appears that the to_number() works correctly when you don't pass it a variable. IE:

to_number ('4.50', '999.99')

... but when used with a variable, it updates with the wrong amount.

to_number ($amountDue, '999.99')

In this case, echoing $amountDue displays 4.50.

You can check the contents of amountDue with the FX specifier; this will print today's date if it contains exactly '4.50', or an error if it doesn't:

select to_date($amountDue,'FX"4.50"') from dual;

Alternatively select dump('4.50') from dual; should print
Typ=96 Len=4: 52,46,53,48

so select dump($amountDue) from dual should print the same if it contains exactly '4.50'.