8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for JSON Support Enhancements in Oracle Database 18c


Camilo said...

I don't think 18c's JSON_VALUE can support a return type of blob object. Per Oracle 18c,

"LOB Results for JSON_VALUE, JSON_QUERY, and JSON_TABLE

SQL/JSON function json_value can now return a CLOB instance.

SQL/JSON function json_query can now return a CLOB or BLOB instance. A BLOB result is in the AL32UTF8 character set."

Am I wrong?

Tim... said...

Hi.

According to this it can.

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

The new features guide is only a summary, so it often misses things out. Even the "what's changed" section in the manuals are summaries too.

Cheers

Tim...

Camilo said...

Hmm. I tried your json_documents table example in Oracle Livesql, and the JSON_VALUE function throw an error when I tried to return a blob. Did I do something wrong?
Select json_value(data, '$.Job' Returning clob) job_type From json_documents;

JOB_TYPE
Clerk

Select json_value(data, '$.Job' Returning blob) job_type From json_documents;

ORA-40449: invalid data type for return value

Viacheslav Andzhich said...

Hi Tim,

Looks like a small typo got into that string:
"The SQL/JSON generation functions JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY and JSON_ARRAY can now return LOB results"
I'm almost sure the last function should be JSON_ARRAYAGG :)

Thank you.
Viacheslav

Viacheslav Andzhich said...

Also, looks like the documentation on JSON_VALUE was updated, there no reference to BLOB there currently (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2).

Tim... said...

Hi.

Corrected typo, and removed the comment about the mistake in the documentation, now they've removed it. :)

Thanks for the heads-up.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.