I just ran into something odd, which I'm afraid might be a bug in APEX itself.
I've created a form with a number field. Because it's going to be holding some big numbers, I assigned a format mask; APEX helpfully provides “999G999G999G999G999G999G990” as a standard. Because the form is driven by a rather complex view, I'm using PL/SQL to handle inserts and updates, rather than the automated row processing. But this doesn't work as easily as you'd think.
The problem is that none of the standard ways of getting the page item's value seem to deal well with numbers with group separators in them (commas, in my instance). :P2_TOTAL and v('P2_TOTAL') both give the text in the field, as expected; oddly enough, nv('P2_TOTAL') throws a number conversion error if there's a comma in the value. I'm afraid this might be a bug in APEX.
My solution was to build my own numeric value function:
create or replace function numeric_val( i_page_item in varchar2 ) return number is l_mask varchar2(500); begin -- Try for the simple case first. begin return to_number(nv(i_page_item)); exception when others then null; end; -- Ok, now check the dictionary. begin select aapi.format_mask into l_mask from APEX_APPLICATION_PAGE_ITEMS aapi where aapi.application_id = v('APP_ID') and aapi.item_name = i_page_item; return to_number(v(i_page_item), l_mask); exception when others then null; end; end;
This attempts first to use APEX's nv( ) function, which works fine if the user just types in “1000” (for example), and catches the exception thrown if the user types in “1,000”. Then I get the field's format mask from the APEX dictionary, and feed that into the to_number call–so “1,000” gets processed correctly. It may not be perfect–this was a five-minute solution, after all–but it gets the job done. (Note: I'm assuming that the page item is unique across the application. A more robust solution would check to see if there are more than one, and if so, pick the one on APP_PAGE_ID. That'd be fairly easy to add in, actually.)