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;

"not working" isn't very helpful. What happens? The first thing that jumps out is that you haven't escaped the single quotes around the date formats... You seem to have lost part of the code too, where you have tried to escape quotes - where has the WORKING' AND sequence_nmbr >= 0` part gone? (Not directly relevant, but what data type is scheduled_launch_date? If it's a date, don't call to_date() for it...) — Alex Poole
This is where formatted code is easier to work with. It is complicated enough without random indentation and upper/lowercase effects. — William Robertson

Dmitriy Dmitriy · Accepted Answer · 2018-01-18T15:55:50

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

SQL QUERY returns no data with ORACLE APEX CLASSIC REPORT

1 Answers