As I pointed out in October 2013 (see ), SQL Developer has a problem with the question mark when used in a MATCH_RECOGNIZE PATTERN clause.
Here is a simple example that works in SQL*Plus:
select * from dual match_recognize(all rows per match pattern( A? ) define a as 1=1)
In SQL Developer 18.1 I still get the error message "Missing IN or OUT parameter at index:: 1"
The underlying problem is that JDBC uses the question mark as a placeholder for a bind variable.
Starting in version 12.1, the driver implemented a (kludgy) escape sequence to work around this problem, see
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdbc/JDBC-reference-information.html#GUID-3454411C-5F24-4D46-83A9-5DA0BA704F5D
In my tests, the documentation appears to be wrong, since it forgets to escape the backslash before the backslash can escape the question mark !!
What works from Java: "select * from dual match_recognize(all rows per match pattern( A{\\?\\}) define a as 1=1)
This syntax is not allowed in SQL Developer: I get the error "ORA-00911: invalid character"
Is there an acceptable syntax, or is this problem still ongoing?
Thanks and best regards, Stew Ashton