We have a business requirement to display negative numbers in parentheses (an accounting convention).
In the 12.1 documentation, it states that we can use the PR format - which indeed does work as described, but uses angled brackets
https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00216
SQL> select to_char(-1000,'fm0000pr') from dual
2 /
TO_CHA
------
<1000>
This doesn't play well with other tools - we need round brackets.
However I found this document online (https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-7BD3C665-851A-43FE-BA2D-7501390D95AC.htm#BIPRD2564 ) which states:
The following table lists the supported Oracle number format mask symbols and their definitions.
and then lists an additional format model "PT":
which works exactly as we want.
SQL> select to_char(-1000,'fm0000pt') from dual;
TO_CHA
------
(1000)
So my question is - is this a documented format model? It's not listed in the SQL documentation, but the middleware documentation says it's a "Supported Oracle format model". Would you use it in Production?
Just for clarification, using any case statements etc isn't possible - we are using APEX and this is the format mask which will be applied to the column. If we convert to a string then you can't use aggregations etc in interactive reports/grids.