APEX

Hello

I'm using Oracle Apex 24.1 over a 21c database. The database has the following parameters for NLS

nls_language string AMERICAN
nls_territory string AMERICA
Over this DB there's a single Apex application which main language is English, but it has a translation to Spanish.

To manage application functions there is a common parameters table which store all the parameters values in a varchar2 column.

To retrieve the parameter values I've defined two functions, one for numeric values, and one for string values.

get_param_n('<param code>') return number;

get_param_s('<param code>') return varchar2;

All the numeric values are stored in the parameter table using the US numeric standard of “,” for thousand separator and “.” for decimal point.

For a report, in a query, I'm using the get_param_n function to retrieve a parameter which has a value 2.59. In the apex English application the query works fine, but when the language is switched to Spanish an ORA-01722: número no válido (Number not valid) is issued.

I tried to force the nls format for the get_param_n function using TO_NUMBER( number,'99999D9999', 'NLS_NUMERIC_CHARACTERS = ''.,''') when the language is Spanish, without success.

Also tried to bypass the NLS functionality using the following code

Function get_param_n(p_code vhars2) return number is
v_pval              varchar2(15);
v_pos               number;
v_num               number;
begin
   select to_number(param_value) into v_pval
   from parameters
   where param_code = upper(p_code) and
         param_datatype='N';

   v_pos:=instr(v_pval,'.');
   
   if v_pos=0 then
       v_num:=to_number(v_pval);
   else
       v_num:=to_number(substr(v_pval,1,v_pos-1))+to_number(substr(v_pval,v_pos+1,99)/power(10,length(substr(v_pval,v_pos+1,99))));
   end if;
         
   return v_num;
end get_param_n;

but it didn't work either.

Appreciate any comment about how to handle this situation.

Thanks