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

Home » Misc » Here

Comments for JSON Support in Oracle Database 12c Release 1 (12.1.0.2)


Sasha said...

Hi!
When declaring JSON_DOCUMENTS with data CLOB NOT NULL, we got an error "ORA-00904: "A"."DATA"."FIRSTNAME": invalid identifier while quering "select a.data.FirstName as FirstName from JSON_DOCUMENTS a"

Do You know, what i the strange behavoiour? (Oracle 12.1)

Tim... said...

Hi.

You can only use dot notation if the IS JSON check constraint is present.

Cheers

Tim...

Sasha said...

Here You are IS JSON check constraint presence. But it seems to be a "constraints concurrency" of "not null" and "is json" leads to ORA-00904...

CREATE TABLE JSON_DOCUMENTS (
id number NOT NULL,
data CLOB not null,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json CHECK (data IS JSON (STRICT))
);

Tim... said...

Hi.

Sounds like a bug. You should open an SR.

Cheers

Tim...

Sasha said...

I'll try an existing patches... livesql works nice,
Tim, would You share Your experience... My JSON table fiels contains a data over 4k... That JSON i can form as i need.
But we don't have a CLOB FORMAT JSON, just VARCHAR2 FORMAT JSON so i can't figure out how to form JSON, so JSON_TABLE can work with and returns over 4k?
Thank You.

Tim... said...

Hi.

Look at the new features in 18c and you will see LOB support has been added to most links in the chain for JSON. Prior to this, much of the functionality was limited to a database VARCHAR2, so that's 4000 bytes be default, or 32767 if you are using extended data types.

Cheers Tim..

Akshay Kumbhar said...

I add column with is json contraint but while inserting I pass null as I dont have data for that column.
It throws constraint violation for that insert statement. any way to handle this scenario without throwing error when there is no data(null)?

Tim... said...

Hi.

Null is not a valid JSON document, but "{}" is. :) This will have to be your NULL if you want the constraint on the column.

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.