SQL & PL/SQL

SQL DOMAIN throw ORA-6504 if used in the define clause of a match_recognize statement.

Database: 23.5 FREE (VirtualBox)

It works inside select clauses within a CTE (with ). It also works in a RAS Realm definition.

create domain tick_d as enum ( start_tick
							 , lap_tick 
							 , end_tick
);


with curr_time (ts) as (
	select systimestamp
),
make_ticks (tick, rn) as (
	-- THESE WORK !!!
	select tick_d.start_tick, interval '0' second union all
	select tick_d.lap_tick, interval '2' second union all
	select tick_d.lap_tick, interval '3' second union all
	select tick_d.lap_tick, interval '4' second union all
	select tick_d.lap_tick, interval '4.2' second union all
	select tick_d.end_tick, interval '13.5' second
), data (ts, tick ) as (
	select c.ts+m.rn, m.tick
	from curr_time c, make_ticks m
)
select * from data
MATCH_RECOGNIZE (
	order by ts

	MEASURES
		last( a_stop.ts ) - first( a_start.ts ) as total_time

	pattern ( a_start a_lap* a_stop )

	define 
		a_start as tick = 1,
		a_lap as tick = 2,
		a_stop as tick = tick_d.end_tick -- THIS FAILS !!!
	)
;