SQL Macros used in Oracle Apex

I've been missing parameterizable views in Oracle for many years.. with SQL macros they have now become possible: (from 19.7)

/* teste sql macros */

with parms(p_von_date,p_bis_date) as 
     (select trunc(sysdate) - 14 p_von_date,
             trunc(Sysdate) p_bis_date  
        from dual) 
select * from trips_and_location('username');

--Welches Statement generiert das SQL Macro?

variable x clob; 

exec dbms_utility.expand_sql_text(q'# with parms(p_von_date,p_bis_date) as 
     (select trunc(sysdate) - 14 p_von_date,
             trunc(Sysdate) p_bis_date  
        from dual) 
select * from trips_and_location('username'); #',:x );

set serveroutput on 
begin dbms_output.put_line(:x); end; 
/

I love SQL macros - they're great for avoiding complex statements in Apex while still dynamically processing parameters.

Unfortunately there are limitations in with clauses, as I have with my macro, these are not processed. Hence the trick with the CTE parms which is defined outside of the sqlmacro. I think and hope the limit will eventually drop.

Here is the code of my SQL table macro (be careful with 19c replace sql_macro(table) with sql_macro)

The main idea of ​​this query is to retrieve information about a user's trips, including start and end times, locations, distances, drive time, idle time, and speed metrics.

This query uses a separate subquery to find the closest locations for the start and end points of each trip. This happens because the geo-coordinates of user locations are collected asynchronously and do not necessarily exactly match the start and end times of a trip.

create or replace function trips_and_location(p_appusername varchar2) return varchar2 sql_macro(table)
is begin
/* with clause cannot have parameters in sql macro (OMG) */
   return q'{

with min_loc_per_day as (
  select appusername,
         status_date,
         replace(latitude, ',', '.') as latitude,
         replace(longitude, ',', '.') as longitude 
  from location_no_odo , parms /* get parameter from outerspace */
  where status_date between parms.p_von_date-1 and parms.p_bis_Date+1 
),
tripinfo_filtered as (
  select appusername,
         tripstart,
         tripend,
         to_number(distance) as distance,
         drivetime,
         idletime,
         speedavg,
         speedmax,
         row_number() over (partition by tripstart order by tripend asc) as rn
  from tripinfo t, parms
  where drivetime > 5 
    and to_number(distance) > 5
    and tripstart between parms.p_von_date and parms.p_bis_Date+1 
),
first_loc as (
  select  row_number() over (partition by t.appusername,tripstart order by tripend asc) as  rnfl,
         tripstart,
         first_value(ml.status_date) over (partition by  t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_status_date,
         to_char(first_value(ml.status_date) over (partition by  t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc), 'HH24:MI') as first_status_time,
         first_value(ml.latitude) over (partition by  t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_latitude,
         first_value(ml.longitude) over (partition by  t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_longitude
  from tripinfo_filtered t
  join min_loc_per_day ml on (t.appusername = ml.appusername)
  where rn = 1
),
last_loc as (
  select  row_number() over (partition by  t.appusername,tripstart order by tripend asc) rnll,
          tripstart,tripend,
         first_value(ml.status_date) over (partition by  t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_status_date,
         to_char(first_value(ml.status_date) over (partition by  t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc), 'HH24:MI') as last_status_time,
         first_value(ml.latitude) over (partition by  t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_latitude,
         first_value(ml.longitude) over (partition by  t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_longitude
  from tripinfo_filtered t
  join min_loc_per_day ml on (t.appusername = ml.appusername)
  where rn = 1
)
select 
       t.tripstart,
       to_char(t.tripstart,'HH24:MI') tripstartdetail,
       to_char(t.tripend,'HH24:MI') tripenddetail,
       first_loc.first_status_date,
       first_loc.first_status_time,
       first_loc.first_latitude,
       first_loc.first_longitude,
       last_loc.last_status_date,
       last_loc.last_status_time,
       last_loc.last_latitude,
       last_loc.last_longitude,
       t.distance,
       t.drivetime,
       t.idletime,
       t.speedavg,
       t.speedmax
from tripinfo_filtered t
join first_loc on t.tripstart = first_loc.tripstart
join last_loc on t.tripstart = last_loc.tripstart
where t.rn = 1 and rnll = 1 and rnfl=1
  and t.appusername = p_appusername /* SQL Macro parameter */
   }';

end trips_and_location;
/

Look now like this

Refresh under 1.5 seconds

yes