Oracle 23ai introduced "JSON-Relational Duality" Views.
With this feature , the existing relational structure can be retrieved
as a JSON document and the data can be stored as JSON documents.
With this approach , the application can use "elastic schema" .
Let us jump into a simple use case / example.
Here is the script for table / record creation.
CREATE TABLE dept (
depid INT PRIMARY KEY,
deptname VARCHAR2(50) NOT NULL
);
CREATE TABLE emp (
empid INT PRIMARY KEY,
empname VARCHAR2(50) NOT NULL,
depid INT,
CONSTRAINT dep_fk FOREIGN KEY ( depid )
REFERENCES dept ( depid )
);
insert into dept values ( 1 , 'HR');
insert into dept values ( 2 , 'DEVOPS');
insert into dept values ( 3 , 'RETAIL');
Insert into emp values ( 101 , 'Zahir Mohideen' ,1 );
Insert into emp values ( 201 , 'John' , 2);
Insert into emp values ( 202 , 'Jane' , 2);
Let us create a simple JSON document from the "emp" table.
When the above SQL is used to create JSON duality view , Oracle greets us with the error.
