Repository navigation
[FEATURE] missing JSON_VALUE function for Oracle #1825
Description
Grammar or Syntax Description
JOSN_VALUEclause 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
Activity
manticore-projects commentedon Jul 24, 2023
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 commentedon Oct 7, 2025
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 commentedon Sep 7, 2026
@manticore-projects The reported JSON_VALUE/RETURNING gap appears resolved on master
6c726d88following #2382. The first exact example now parses as a structuredJsonFunctionwith typeVALUEand returning typeNUMBER: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, orDEFAULT literal ON ERROR. BareON ERRORstill 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 bothtoString()andStatementDeParser. 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 Gradlecheckpassed.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.