SQL> select to_char(to_date(34, 'j'), 'jspth') spell from dual;
SQL> select to_char(to_date(1023, 'j'), 'jspth') spell from dual;
one thousand twenty-third
Lovely! Nevertheless, there are some limitations here, and some of them are quite ugly therefore they should be carefully analyzed before deciding to use this approach into a production system. The following are some of the problems you might encounter:
- it's not suitable for multi-language applications. The SP prefix always speaks in English, despite of the current NLS settings.
- It cannot be used to display large numbers. In fact, the maximum number which can be spelled using this approach is exactly 5373484. It's quite small for a bookkeeping system, right?
- How about negative numbers or zero? Forget it! It will not work!