Oracle Apex Date Picker behaving strangely

Question

I am using Oracle Apex 19.2. I am trying to build a a dynamic report based on Classic report and PL/SQL function body returning SQL statement. Simply I have two date pickers where I set a default value of sysdate - 21 (set through dynamic action on page refresh) then I construct a simple report with the following PL/SQL

declare
  clsd_snc date:= :P5_CLOSED_SINCE;
  ignrd_snc date := :P5_IGNORED_SINCE;
begin
  return 'select ' || clsd_snc || ',' || ignrd_snc || ' from dual';
end;

I have two problems:

When the form loads I see the following set in the date pickers: 12/15/2019
When I set the date manually to a proper date I see the following output in the report: .000495049504950495049504950495049504950495

I tried using to_date and to_char in the PL/SQL and to change the date format of the pickers but nothing is working. I would hugely appreciate your help.

I tried to do what you described, but can't reproduce it - works OK for me. Please, create a sample page on apex.oracle.com, provide login credentials so that we could look at what you did and how Apex responded. — Littlefoot
Please login to the following account Workspace:TRACKING_WS Username:test@user.com Password:APEX1234 Page 3 in the report — Ahmed Sol

Littlefoot Littlefoot · Accepted Answer · 2020-01-06T10:08:57

Thank you for the sample page. I copied your page 3 to page 4 (so that your "original" remains intact) and did this:

dynamic action: turn OFF "Escape special characters"
- this fixes the 12/15/2019 issue

report query: enclose clsd_snc into single quotes (chr(39))

this fixes the .000495049504950 issue

declare
  clsd_snc date := :P4_CLSD_SINCE;
begin
  return 'select ' || chr(39) || clsd_snc || chr(39) || ' from dual';
end;

I guess that's it; so - check page #4.

Oracle Apex Date Picker behaving strangely

1 Answers