Date picker invalid number

Question

in my application I've got a page, on which the user should be able to select a date through a date picker. This date should be stored in a column called 'TASK_END_DATE' as a varchar2(50).
Because on this page, theres already a process to store some other entered information in my table, so the date picker needs to be of source 'Database_Column'.
But now my problem is, that I'll get this error while laoding the page and I have absolutely no idea how to solve it.

ORA-01722: invalid number

Contact your application administrator. Details about this incident are available via debug id "14280". Technical Info (only visible for developers)

is_internal_error: false
ora_sqlcode: -1722
ora_sqlerrm: ORA-01722: invalid number
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 30555528155357856
component.name: Fetch Row from SEEXML_TASKS
error_backtrace:

ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "APEX_180100.WWV_FLOW_DYNAMIC_EXEC", line 2360
ORA-06512: at "APEX_180100.WWV_FLOW_DYNAMIC_EXEC", line 1406
ORA-06512: at "APEX_180100.WWV_FLOW_DML", line 827
ORA-06512: at "APEX_180100.WWV_FLOW_PROCESS_NATIVE", line 556
ORA-06512: at "APEX_180100.WWV_FLOW_PROCESS_NATIVE", line 1157
ORA-06512: at "APEX_180100.WWV_FLOW_PLUGIN", line 2451
ORA-06512: at "APEX_180100.WWV_FLOW_PROCESS", line 201

error_statement:

begin begin  select "TASK_LATEST_DELIVERY","TASK_STATUS_FLAG","LATEST_MANIPULATOR",to_char("TASK_END_DATE", :p$_format_mask1),"TASK_NAME","TASK_PARTNER","TASK_ID","TASK_STATUS","TASK_CREATOR","TASK_MASTERID","INITIAL_ORDER_NUMBER","INITIAL_REQUESTER","TASK_DEV_COMMENT","TASK_PROJECT_ENTRY" into wwv_flow.g_column_values(1),wwv_flow.g_column_values(2),wwv_flow.g_column_values(3),wwv_flow.g_column_values(4),wwv_flow.g_column_values(5),wwv_flow.g_column_values(6),wwv_flow.g_column_values(7),wwv_flow.g_column_values(8),wwv_flow.g_column_values(9),wwv_flow.g_column_values(10),wwv_flow.g_column_values(11),wwv_flow.g_column_values(12),wwv_flow.g_column_values(13),wwv_flow.g_column_values(14) from "SEEXML_TASKS" where "TASK_ID" = :p_rowid; end;
end;

If I don't choose 'Database column' as source, it works well but doesn't update the new value in the table.
So maybe there is a suggestion in how to use a page item in a process without setting it's source to a 'Database column'? I would appreciate any suggestions that may solve my problem!
Cheers!

in future, please copy-paste the error_statement from your error page into your question, as your screenshot has chopped off a large portion of the code. In this case, it's lucky that we can see the problematic portion. — Jeffrey Kemp

Jeffrey Kemp Jeffrey Kemp · Accepted Answer · 2018-12-04T02:02:35

If TASK_END_DATE is a VARCHAR2, your expression to_char("TASK_END_DATE", :p$_format_mask11) will result in "ORA-01722 invalid number".

to_char expects either a date or a number. Since you provided a varchar2, it must first do an implicit conversion of the string to a number, and this fails with ORA-01722.

It is recommended that a table column that needs to store a date value be created with the type DATE, not VARCHAR2. Then, you can convert it to a string format for use in the APEX form using to_char.

Date picker invalid number

1 Answers