6 Replies Latest reply on May 15, 2018 10:17 PM by Stew Ashton

    MATCH_RECOGNIZE: still can't use question mark in SQL Developer 18.1

    Stew Ashton

      As I pointed out in October 2013 (see [4.0 EA2] can't use MATCH_RECOGNIZE "reluctant qualifiers"  ), 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



      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