SQL QUERY returns no data with ORACLE APEX CLASSIC REPORT

Question

I have a SQL Query (PL/SQL function body returning SQL Query) in Oracle application express 4.2. I created a classic report and this is the query I am using below. My query currently works fine but I need to update the existing query in this function with another query and its not working when I try to change the query. What am I doing wrong? Any help is appreciated.

Here is my working query below:

declare
  a_query varchar2(5000);
  this_strin varchar2(50);
begin
  a_query := 'select flight_nmbr, sequence_nmbr '||
         'from flights '||
         'where sequence_nmbr >= 0 and data_version_name = ''' ||
    :P3_DATA_VERSION || '''';
  if :P3_SEARCH_NUMBER is not null then
    if instr(:P3_SEARCH_NUMBER, '%') > 0 then
      this_strin := :P3_SEARCH_NUMBER;
  else
      this_strin := '%'||:P3_SEARCH_NUMBER||'%';
  end if;
  a_query := a_query||chr(10)||
            ' and flight_nmbr like '''|| upper(this_strin) ||'''';
  end if;
  return a_query;
    end;

I need to update the query inside this function to this below:

SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM FLIGHTS
WHERE DATA_VERSION_NAME = 'WORKING' AND
  sequence_nmbr >= 0
ORDER BY (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
AND add_months(trunc(sysdate, 'MON'), 6) THEN 1 ELSE 2 END),
     (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
     AND add_months(trunc(sysdate, 'MON'), 6) 
THEN SCHEDULED_LAUNCH_DATE END),
     sequence_nmbr;

I tried to do this myself and this is what I got below (This does not work):

   declare
       a_query varchar2(5000);
    this_strin varchar2(50);
   begin
      a_query := 'select flight_nmbr, sequence_nmbr '||
         'from flights '||
         'where sequence_nmbr >= 0 and data_version_name = ''' ||
         'ORDER BY (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY'
          BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
          AND add_months(trunc(sysdate, 'MON'), 6) THEN 1 ELSE 2 END),
          (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') BETWEEN 
           add_months(trunc(sysdate, 'MON'), 0) 
     AND add_months(trunc(sysdate, 'MON'), 6) THEN
             SCHEDULED_LAUNCH_DATE END),
             sequence_nmbr' ||
      :P3_DATA_VERSION || '''';
        if :P3_SEARCH_NUMBER is not null then
        if instr(:P3_SEARCH_NUMBER, '%') > 0 then
           this_strin := :P3_SEARCH_NUMBER;
        else
             this_strin := '%'||:P3_SEARCH_NUMBER||'%';
        end if;
          a_query := a_query||chr(10)||
            ' and flight_nmbr like '''|| upper(this_strin) ||'''';
        end if;
         return a_query;
          end;

Answer 1

It doesn't work because you have a lot of symbols ' in the query. You need to duplicate each quote (you did this only for the first and the last), or to use the following trick:

a_query := q'[SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
    FROM FLIGHTS
    WHERE DATA_VERSION_NAME = 'WORKING' AND
      sequence_nmbr >= 0
    ORDER BY (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
    BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
    AND add_months(trunc(sysdate, 'MON'), 6) THEN 1 ELSE 2 END),
         (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
    BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
         AND add_months(trunc(sysdate, 'MON'), 6) 
    THEN SCHEDULED_LAUNCH_DATE END),
         sequence_nmbr]';

A text inside the q'[ ... ]' can contain single qoutes and will be considered as a string value with qoutes inside.

UPD
A small example to demostrate the idea:

select q'[I'm here]' text from dual;

TEXT   
--------
I'm here