Oracle second date range comparison ignored in where clause

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause):

create or replace function        func_get_Open_Days_2
   ( ARG_SUBMITTED_BY_PERSON_ID in number,
  ARG_SUBMISSION_DATE in date,
  ARG_FORM_VERSION_DATE_TIME in timestamp,
  ARG_FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT 
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = ARG_SUBMITTED_BY_PERSON_ID and
        t.form_id = ARG_FORM_ID and
        t.submission_date = ARG_SUBMISSION_DATE and
        t.form_version_date_time = ARG_FORM_VERSION_DATE_TIME and 
        t.submission_status_code NOT IN (3469, 3462);
      order by t.status_date asc;

Preserve the correct sequence

I finally figured out the problem. The way how I extract all the gzipped log files and add them to a consolidated log file did not preserve the correct sequence and therefore pt-query-digest did not find the queries in the order it was expecting them.

The manual even says:

Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log

Solution

The solution is to process the logrotate files in reverse order in order to end up with a logfile that has the oldest logs first and the newest ones at the end of the file.

ls mysql-slow.log* |sort -nr -t . -k 3,3 | xargs zcat -f > MySQL-All-Slowlogs.log

The command above does the following things:

1. ls: list all files starting with the file name mysql-slow.log.*
2. sort -nr -t: sort based on a numeric literal (otherwise the sequence number of the file will be interpreted as string alphabetically, sorting 111 before 2. -r reverses the order. -t uses "." as field seperator. k specifies the key to sort on.
3. xargs: For each line cat the zipped or unzipped slow log file to the culmulative file.

Then you can use the following comand to obtain

 pt-query-digest --since '2017-04-06 12:00:00' --until '2017-04-09 12:00:00'  MySQL-All-Slowlogs.log  > output.txt