7

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:

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Don't use a `PreparedStatement`? –  Feb 08 '17 at 08:45
  • @a_horse_with_no_name: Let's assume instead of `dual`, I have a super complicated derived table with tons of bind variables in it because SQL injection etc. – Lukas Eder Feb 08 '17 at 08:59

1 Answers1

7

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • If you are putting the escape sequence into a Java string then you need to escape the \ character inside the Java string so the full sequence would be `{\\?\\}`. This is clearer in the [OJDBC documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-reference-information.html#GUID-3454411C-5F24-4D46-83A9-5DA0BA704F5D). – MT0 Mar 16 '22 at 09:57