CODEWITHSUNDEEP
oracle-databaseoracle-apexnumber-formatting
Necro
Necro

Reputation: 11

Insert white space in oracle APEX currency format mask

I'm using oracle APEX 20.1 and have a report with a column that displays currency values. Since I'm from Europe I need the currency symbol to be behind the value (unlike the typical $amount format you see often in America).

I achieved this by changing the column's format mask to 'FM999G999G999G999G990D00L'. This works well so far, but visually I would prefer if there was a whitespace between the end of the number (D00) and the currency symbol (L). Is there a way to insert a static whitespace in this format string?

I already looked through oracle's documentation on currency format strings, but they do not seem to mention such an option to include an always-there whitespace or an arbitrary static character.

Thank you in advance.

Upvotes: 1

Views: 883

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You can't include punctuation or character literals in a number mask as you can for dates, unfortunately.

You can include a space as part of the currency symbol itself - that is a string, not a character, and can be up to 10 bytes:

to_char(<number>, 'FM999G999G999G999G990D00L', 'nls_currency='' $''')

though that then uses a fixed currency symbol, not the session value from the L format element; you can get it dynamically from the session parameters:

to_char(
  <number>,
  'FM999G999G999G999G990D00L',
  (
    select 'nls_currency='' ' || value || ''''
    from nls_session_parameters where parameter = 'NLS_CURRENCY'
  )
)

which is a bit ugly. And you might not actually want the session's currency symbol; it might be more appropriate to always use the symbol that's relevant to that data.

And I imagine neither of those will fit in to Apex's numeric column formatting, so you would probably need to do that to_char() call explicitly in your query and have Apex just treat it as a pre-formatted string. (I have no idea how formatting works in Apex - from your description I'm assuming there is somewhere you define a format mask for a column in an interactive grid or whatever; but maybe you are already calling to_char().)

You can also change the currency symbol for the session:

alter session set nls_currency = ' €';
select to_char(123.45, 'FM999G999G999G999G990D00L') from dual;

123.45 €

which might be an option but would affect all currency fields - where the L format element is used - though maybe that's a good thing.

db<>fiddle.

Upvotes: 5

Related Questions