What is the right way to avoid? character in JDBC PreparedStatement when using Oracle 12c MATCH_RECOGNIZE?

Question

The following query is true in Oracle 12c:

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

But it does not work through JDBC because of the character ? , which is used as a regular expression symbol, and not as a binding variable.

What is the right way to avoid ? via JDBC, if I want to run this as a PreparedStatement with links?

Note:

• I found a discussion about the JDBC mailing list, but there is no solution to this problem: http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-October/000066.html li>
• PostgreSQL has similar problems with JSON statements: How to use PostgreSQL JSON (B) statements containing a question mark "" "via JDBC

Answer 1

This is explicitly described in the documentation.

https://docs.oracle.com/database/121/JJDBC/apxref.htm#CHECHCJH

MATCH_RECOGNIZE clause

What? The character is used as a token in the MATCH_RECOGNIZE clause in Oracle Database 11g and later. How does the JDBC standard define? a symbol as a parameter marker, the JDBC driver and SQL Server cannot distinguish between different uses of the same token.

In earlier versions of the JDBC driver, if you want to interpret? as a MATCH_RECOGNIZE marker, and not as a parameter marker, then you should use Statement instead of PreparedStatement and turn off evacuation processing. However, starting with Oracle Database 12c Release 1 (12.1.0.2), can you use the syntax "{\ ...}" when using? character, so the JDBC driver does not treat it as a parameter marker and allows the SQL handler to process it.