What's the correct way to escape the ? character in a JDBC PreparedStatement when using Oracle 12c MATCH_RECOGNIZE?

Question

The following query is correct in Oracle 12c:

SELECT *
FROM dual
MATCH_RECOGNIZE (
  MEASURES a.dummy AS dummy
  PATTERN (a?)
  DEFINE a AS (1 = 1)
)

But it doesn't work through JDBC because of the ? character that is used as a regular expression character, not as a bind variable.

What's the correct way to escape the ? through JDBC, assuming I want to run this as a PreparedStatement with bind variables?

Note:

• I've found a discussion on the JDBC spec discuss mailing list, but there's no conclusion to this problem: http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-October/000066.html
• PostgreSQL has similar problems with JSON operators: How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC

Answer 1

This is covered explicitly in the documentation:

MATCH_RECOGNIZE Clause

The ? character is used as a token in MATCH_RECOGNIZE clause in Oracle Database 11g and later versions. As the JDBC standard defines the ? character as a parameter marker, the JDBC Driver and the Server SQL Engine cannot distinguish between different uses of the same token.

In earlier versions of JDBC Driver, if you want to interpret the ? character as a MATCH_RECOGNIZE token and not as a parameter marker, then you must use a Statement instead of a PreparedStatement and disable escape processing. However, starting from Oracle Database 12c Release 1 (12.1.0.2), you can use the '{\ ... \}' syntax while using the ? character, so that the JDBC driver does not process it as a parameter marker and allows the SQL engine to process it.