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

      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