Use Cases

The match_recognize clause is useful to implement the following use cases:

Finding series of consecutive events (see slides)

Pattern matching: trend reversal, periodic events, …

Top-N per Group (see slides)

Compatibility

Row pattern matching was introduced by SQL:2016 with three optional features: R010 is the basis: this feature requires the match_recognize clause as explained above with the aggregate functions min , max , sum , count , avg . R020 allows using patterns in the over clause to define frames. R030 supports all aggregate functions for row patterns (e.g. stddev_pop , …).

JDBC and ORA-17041 Missing IN or OUT parameter

The character ? is used as placeholder in JDBC but is also a valid literal in the pattern clause (e.g., for non-greedy matching). Per default, the Oracle JDBC driver treats each ? as bind parameter. To use ? as literal in the pattern clause, the Oracle JDBC driver supports two methods: