[FEATURE] missing JSON_VALUE function for Oracle

[ZhengguanLi]

Grammar or Syntax Description

• JOSN_VALUE clause is not supported yet

SQL Example

• Simplified Query Example, focusing on the failing feature

  SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
  FROM DUAL;
  
  SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER ON ERROR) AS value
  FROM DUAL;

  Exception:

  net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
      at line 1, column 18.
  Was expecting one of:
      "&"
      "::"
      "<<"
      ">>"
      "EXCEPT"
      "INTERSECT"
      "INTO"
      "MINUS"
      "UNION"
      "["
      "^"
      "|"
      <EOF>
      <ST_SEMICOLON>
  

Additional context

The used JSQLParser Version: 4.7_SNAPSHOT.
Oracle DB 12c
Links to the reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

[manticore-projects]

The most recent documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

[ANeumann82]

MySQL: https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
DB2: https://www.ibm.com/docs/en/db2/12.1.0?topic=functions-json-value
Postgres: https://www.postgresql.org/docs/current/functions-json.html#SQLJSON-QUERY-FUNCTIONS

[minleejae]

@manticore-projects The reported JSON_VALUE/RETURNING gap appears resolved on master 6c726d88 following #2382. The first exact example now parses as a structured JsonFunction with type VALUE and returning type NUMBER:

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
FROM DUAL;

There is one qualification: the second example contains bare RETURNING NUMBER ON ERROR. Oracle's documentation requires an action: NULL ON ERROR, ERROR ON ERROR, or DEFAULT literal ON ERROR. Bare ON ERROR still fails, but is not a valid form under that grammar.

I separately tested all three valid forms (using DEFAULT 0), checked their AST behavior fields, and verified round-trips through both toString() and StatementDeParser. The original first example and these valid variants pass with default settings and unsupported-statement fallback disabled. Existing coverage includes JsonFunctionTest.testJsonValue, and the full Gradle check passed.

Could you consider closing this issue for the reported gap, unless there is another valid Oracle reproducer? This is not a claim that every Oracle JSON_VALUE option is supported.