0

Can anyone explain what is wrong with this SQL?

SELECT TO_CHAR(1890.55,'$9,99D9V99') FROM DUAL;

it throws an error:

ORA-01481: invalid number format model
01481. 00000 - "invalid number format model
Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter.
Action: Consult your manual.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Rahul
  • 49
  • 8
  • Edit your question and provide sample data and desired results. You are more likely to get an answer if we know what you want to do. – Gordon Linoff Jul 30 '17 at 16:13

2 Answers2

1

Your format string doesn't specify enough digits to the left of the decimal specifier (D) for the number you've given. In addition, you can't mix , and D formatting characters in the same format string, and you can't have more than one of V or D or . in a format string. You can either use

$9,999.99

or

$9G999D99

or

$9,999V99

although the last one, obviously, won't show a decimal point.

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
0

Use this;

select to_char(1890.55,'fm$999G990D00','NLS_NUMERIC_CHARACTERS = ''.,''') from dual
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55