Apex 23.1.4
Having an issue with an interactive grid.
My interactive grid has a payment amount column which is type NUMBER.
When i enter an amount over with a comma like 1,000 and try to save, i get an error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
Any amount less than 1000 saves with no problem.
This only happens on new rows or inserted rows(by hitting add row)
Here is my interactive grid query
--create default query to setup initial default null values for interactive grid
select
PAYMENT_DETAILID,
PROJECTED_DATE,
AMOUNT,
FROM
(
SELECT
0 as PAYMENT_DETAILID,
null as PROJECTED_DATE,
0 as AMOUNT,
FROM dual
where rownum = 0
);
For new records i have a onload javascript which populates the GRID based on what the user selected on a previous screen.
The user can then manipulate the grid and save.
When the user saves the record, it executes the following page process:
--this function removes any commas from the amount
pkg_valid_amount (
amount_text => :AMOUNT --pass in the amount
,is_valid => l_is_addl_valid --this will return TRUE or FALSE
,amount => l_schedule_amount --this will strip the amount field if any comma's
--it will also set the l_schedule_amount variable
);
case :APEX$ROW_STATUS
when 'C' then
if l_schedule_amount is NOT null THEN
pkg_insertPayment(
,p_projected_date => sysdate
,p_projected_amount => to_number(l_schedule_amount , '999G999G999G999G990D00')
);
end if;
end case;
I get the error ORA-06502: PL/SQL: numeric or value error: character to number conversion
If i enter 999 and below on the AMOUNT column on the grid, it works fine.
I changed the page process code to this(hard coded 800), but i left the AMOUNT on the GRID as 1,000
pkg_insertPayment(
,p_projected_date => sysdate
,p_projected_amount => to_number(**800** , '999G999G999G999G990D00')
);
I get the same error.
My debugger shows this:
Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error - Additional info: ORA-06502: PL/SQL: numeric or value error: character to number conversion error - Display location: INLINE_IN_NOTIFICATION - Association type: - Item name: - Region id: - Column alias: - Row: - Model instance id: - Model record id: - Internal error: false - Common runtime error: false - APEX error code: - SQL code: -6502 - SQL error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error - Backtrace: - Statement: (--create default query to setup initial default null values for interactive grid select PAYMENT_DETAILID, PROJECTED_DATE, AMOUNT FROM ( SELECT 0 as PAYMENT_DETAILID, null as PROJECTED_DATE, 0 as AMOUNT FROM dual where rownum = 0 ) ) - Component: APEX_APPLICATION_PAGE_PROCESS Insert_PAYMENT_DETAIL
So i'm wondering is it my GRID query that's causing the issue?
Any ideas?