Can you explain the difference between the following queries
SELECT TO_CHAR(1980.55,'$9,999D999') FROM DUAL;
and
SELECT TO_CHAR(1980.55,'$9G999D999') FROM DUAL;
The first query fails with an error, what I don't understand is that the group separator defaults to ',' so why does one fail and the other execute successfully.
Interesting, haven't come across that before, but then can't imagine why you'd want to mix them. I can't find anything in the documentation or even on MSO, so I'll speculate...
The ORA-01481 error you get from mixing comma/period with the group separator/decimal characters (G or D) is 'invalid number format model', which suggests it's purely validating the string '$9,999D999'. It appears that at this stage it is not aware of your NLS_NUMERIC_CHARACTER setting, even if it's passed in to the to_char() function as the optional third argument. That doesn't seem too unreasonable since the same validation would apply in stored code (procedure, package, view or whatever), and you wouldn't expect a cached query to be revalidated based on the NLS settings (i.e. a hard parse).
So if at the point that format model string is validated it doesn't know how G and D will be evaluated at runtime, the restrictions listed in the documentation become a problem. Is your comma supposed to be a group separator or a decimal character? If at run-time your decimal character is . then the format would be invalid as you can only have one of those. Similarly if the model was '9.999G999' then it would be valid if your group separator is ., but not if it is ,. Failing at parse time rather than run time seems safer and more reliable.
Or to put it another way, the point where 'the group separator defaults to ,' is after the format model has been validated, so it doesn't make any difference.
But, as I say, speculating on the internals, which is never a very good idea. The documentation seems to be a bit misleading when it says a 'comma [or group separator] cannot appear to the right of a decimal character or period in a number format model', since it they can't coexist at all. Might be worth raising a documentation bug?
A similar issue was raised as bug 1204892 against 8i, and closed as not-a-bug with comment 'This is the way it's designed to work'.