APEX

Hi Experts,

We're using Apex 22.2 and we have a D.A. (change item) with a Set Value action with this SQL:

SELECT SUM(ptinv_total_plani_fee_c) + :P13_PTINV_TOTAL_PLANI_FEE_C 
 FROM mplae_cal
WHERE cdmplae_lin_id IN (SELECT cdmplae_lin_id
                           FROM mplae_lin
                          WHERE cdmplae_cab_id = :P13_CDMPLAE_CAB_ID)
  AND cdmplae_cal_id != :P13_CDMPLAE_CAL_ID

We get the ORA-01722: invalid number, so we assume is a missing to_number and we add it:

SELECT SUM(ptinv_total_plani_fee_c) + to_number(:P13_PTINV_TOTAL_PLANI_FEE_C, '999G999G990D00')
 FROM mplae_cal
WHERE cdmplae_lin_id IN (SELECT cdmplae_lin_id
                           FROM mplae_lin
                          WHERE cdmplae_cab_id = :P13_CDMPLAE_CAB_ID)
  AND cdmplae_cal_id != :P13_CDMPLAE_CAL_ID

But still getting the error. Changed to this SQL and now it's working fine:

SELECT SUM(ptinv_total_plani_fee), 
to_number(:P13_PTINV_TOTAL_PLANI_FEE, '999G999G990D00', 'NLS_NUMERIC_CHARACTERS='',.''')
 FROM mplae_cal
WHERE cdmplae_lin_id IN (SELECT cdmplae_lin_id
                           FROM mplae_lin
                          WHERE cdmplae_cab_id = :P13_CDMPLAE_CAB_ID)
  AND cdmplae_cal_id != :P13_CDMPLAE_CAL_ID

I'd like to understand the root cause, because the Apex application is defined like this:

Checked into debug that the decimal and thousand separator, are in Spanish lang is:

And also, values in session have the appropriate separators:

Why do we need to force the NLS in the to_number?

Thanks,
Jose.