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:

1. When the form loads I see the following set in the date pickers: 12/15/2019
2. 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.

Answer 1

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.