Oracle Apex Error

Question

begin
INSERT INTO REI_LABOUR_RATE
SELECT
    LR.BILLING_PARTNER AS DEALER_NUMBER,
    'GW' as BSI_GW,
    NULL as FLEET_DISCOUNT,
    NULL as RETAIL_RATE,
    LR.REQUESTED_RATE as REQUESTED_RATE,
    :P6_APPROVED_RATE as APPROVED_RATE,
    LR.REQUEST_DATE as REQUEST_DATE,
    :P6_APPROVAL_DATE as APPROVAL_DATE,
    to_char(sysdate,'DD.MON.YYYY') as RECORD_DATE,
    LR.NOTES as NOTES
FROM REI_LABOUR_RATE_REQUEST LR
WHERE BILLING_PARTNER = :P6_FILTER_DEALER;

DELETE FROM REI_LABOUR_RATE_REQUEST
WHERE BILLING_PARTNER = :P6_FILTER_DEALER;

end;

After executing a Dynamic Action with this SQL code I get this error:

Ajax call returned server error ORA-01858: a non-numeric character was found where a numeric was expected for Execute PL/SQL Code.

Where did I screw up exactly? I bet it has something to do with the sysdate.

Error clearly says you are trying to insert a non-numeric character to number field of table 'REI_LABOUR_RATE' . Check if you are inserting the same sequence as columns of the table — XING
For testing purposes, I casted all number values to int, still the same error. — Markus Appel
I casted all number values to int, how does it address the issue where it says you are inserting a non-numeric character to numeric field. It means you are trying to insert char to inr or num column — XING
What is the data type of the column in REI_LABOUR_RATE into which you are inserting to_char(sysdate,'DD.MON.YYYY')? — Tony Andrews

Iulian Iulian · Accepted Answer · 2017-09-07T08:25:35

The apex items with type Date Picker must be converted when inserted into the database.

If the apex item :P6_APPROVAL_DATE is a Date Picker field, then you must convert the value like: TO_DATE(:P6_APPROVAL_DATE, 'DD.MON.YYYY')

Oracle Apex Error

2 Answers