Error in Process code: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Question

Here is my Plsql code used inside the process code, but i am getting the error as char to number conversion, that to due to l_receipt_date column I need to insert the date as 'YYYY-MM-DD' format as mentioned below so i used the below code inside process,l_receipt_date:=to_char(:P4_RECEIPT_DATE,'YYYY-MM-DD'); ,i am getting error because of this above line.Here is my Plsql code used inside the process code, but i am getting the error as char to number conversion, that to due to l_receipt_date column

DECLARE
    l_clob            CLOB;
    l_emp_no          NUMBER;
    l_status          VARCHAR2(100);
    l_employee_name   VARCHAR2(100);
    l_id              NUMBER;
    l_employee_salary NUMBER;
    l_employee_age    NUMBER;
    l_request_url     VARCHAR2(200);
    l_body_clob    clob;
    x_err varchar2(2000);
    l_receipt_date  varchar2(1000);
begin

l_request_url := 'https://fa-eoxd-test-saasfaprod1.fa.ocs.oraclecloud.com/fscmRestApi/resources/latest/standardReceipts/';
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';


l_receipt_date:=to_char(:P4_RECEIPT_DATE,'YYYY-MM-DD');


l_body_clob:='{
"ReceiptNumber":"'||:P4_RECEIPT_NUMBER||'"
,"BusinessUnit":"'||:P4_OPERATING_UNIT_NAME||'"
,"ReceiptMethod":"'||:P4_RECEIPT_METHOD||'"
,"ReceiptDate":"'||l_receipt_date||'"
}';
    
 l_clob :=                                           
APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url  => l_request_url,
    p_http_method  => 'POST',
    p_username     => 'fin.user',
    p_password    => 'Fusion@123',
    p_body => l_body_clob) ;  

htp.p(l_clob);
exception when others then 
x_err:=sqlerrm;
htp.p(x_err);
END; 

Answer 1

P4_RECEIPT_DATE is a page item, and all page items are character strings. So before you can TO_CHAR it to a different formatted string, you first need to TO_DATE it to a date. Suppose it is currently in the format DD-MON-YYYY. Then you will need to do this:

l_receipt_date:=to_char(to_date(:P4_RECEIPT_DATE, 'DD-MON-YYYY'),'YYYY-MM-DD');

i.e.

1. Take the string in P4_RECEIPT_DATE and convert it to a DATE using format DD-MON-YYYY.
2. Take that date and convert it to a string again in format YYYY-MM-DD.