21.11 Using the Converter With Defined JSON Schema
You can create a JSON schema for a table without needing to load JSON data into the database, or with implied schema from your collections of data.
- About Using JSON Schemas to Infer a Relational Schema
To enhance your control over the migration process, you can use the JSON-to-Duality Migrator to infer a relational schema from multiple JSON schemas. - Choices to Infer a Relational Schema Using JSON-to-Duality Converter
You can choose to infer a relational schema using JSON schemas defined on the input tables, or the data in the input tables, or both. - Example Using a JSON Schema Without Input Data to Infer a Relational Schema
We define a JSON schema for the JSON column in the input table without inserting any data. The converter functionINFER_AND_GENERATE_SCHEMAgenerates the relational schema based solely on the structure and constraints specified in the provided JSON schema. - Example Using a JSON Schema With Input Data to Infer a Relational Schema
We define a JSON schema for the JSON column and insert data into the input table at the same time. TheINFER_AND_GENERATE_SCHEMAfunction uses both the schema and the data to create a more accurate and complete relational database schema.
Parent topic: Migrating From JSON To Duality
21.11.1 About Using JSON Schemas to Infer a Relational Schema
To enhance your control over the migration process, you can use the JSON-to-Duality Migrator to infer a relational schema from multiple JSON schemas.
The JSON-To-Duality Migrator supports input tables with predefined JSON schemas for enhanced migration flexibility. You can define JSON schemas for JSON columns in input tables, and you can choose to skip data insertion before you start the converter functions. This capability provides greater control over the migration process, and enables schema inference from multiple sources.
With JSON schema definition support, the converter functions
INFER_AND_GENERATE_SCHEMA and INFER_SCHEMA can
operate in three distinct modes: JSON schema-only conversion, data-only conversion, and
combined JSON schema-data conversion. The converter intelligently uses the available
inputs—whether JSON schema definitions, inserted JSON data, or both—to generate accurate
and comprehensive database relational schemas that represent the underlying tables and
JSON duality views.
Parent topic: Using the Converter With Defined JSON Schema
21.11.2 Choices to Infer a Relational Schema Using JSON-to-Duality Converter
You can choose to infer a relational schema using JSON schemas defined on the input tables, or the data in the input tables, or both.
The converter function analyzes the provided JSON schema, inserted JSON data, or both inputs to generate the corresponding relational database schema. This relational database schema represents the database tables and JSON duality views required to support the document sets. When both schema and data are available, the converter combines the explicit schema constraints with the inferred data patterns to produce optimized database structures.
Review examples in this document to see the details about the differences between these options. At a high level, these are the characteristics of each of these choices:
-
JSON Schema-Only Conversion: Defines a JSON schema for the JSON column without inserting data into the table. The converter generates the relational schema based solely on the provided JSON schema structure and constraints.
Use this option when you want to develop your applications by generating the relational schema before importing actual data. This is particularly useful when you need to quickly establish the database structure, especially when working with large data sets and want to verify the schema design before committing to data migration.
- JSON Data-Only Conversion: Use this option when you have existing JSON documents but no predefined JSON schema. Selecting this option allows the converter to automatically infer the relational schema from your actual data. For an example of this option, see Migrating To Duality, Simplified Recipe
- Combined JSON Schema-Data Conversion: Provide both a JSON schema definition and data for the JSON column. The converter functions use both inputs to generate a more accurate and comprehensive relational database schema, leveraging the explicit JSON schema constraints with the actual data patterns. Use this option when you want the most accurate and optimized relational schema generation, when your JSON schema and actual data may have slight variations that need to be accommodated.
JSON Schema-Only Conversion Characteristics
Defining JSON schemas by using a JSON schema-only conversion for input tables provides several advantages during the conversion process:
- Enables early validation of document structure
- Enables early validation of constraints before data insertion.
Combined JSON Schema-Data Conversion Characteristics
With combined JSON schema-data input, the converter successfully accommodates schema-data differences:
- Schema-defined fields not in data: Schema fields, such as
locationin course schema andemailIdinteacherschema create corresponding columns in database root tables. - Data fields not in schema: Data fields, such as
phoneNumberinteacherdata (omitted from our example schema for brevity) are derived from actual data, and included in the generated schema. - Enhanced normalization: Data analysis during the conversion process enables better database table relationships and more efficient storage structures.
Parent topic: Using the Converter With Defined JSON Schema
21.11.3 Example Using a JSON Schema Without Input Data to Infer a Relational Schema
We define a JSON schema for the JSON column in the input table without
inserting any data. The converter function INFER_AND_GENERATE_SCHEMA generates
the relational schema based solely on the structure and constraints specified in the provided
JSON schema.
This example demonstrates creating input tables with JSON schema definitions without inserting data before calling the converter functions. The JSON schemas define the structure and constraints for student, teacher, and course documents, matching the expected input data format. The input files are in the topic School Administration Example, Migrator Input Documents.
Note:
When using schema-only conversion, keep in mind that tables across different collections may not be normalized optimally, because the converter lacks actual data patterns for analysis. Any relational schema generated usingINFER_AND_GENERATE_SCHEMA uses auto-generated primary keys
(ora$id) instead of natural keys identified from actual data content.
Example 21-23 Defining a JSON Schema on Input Table Without Data
The following SQL code creates three input tables with comprehensive JSON schema validation. Each table includes detailed property definitions, data types, and required field specifications:
-- Create student_tab input table with JSON schema
DROP TABLE IF EXISTS student_tab;
CREATE TABLE student_tab (
data JSON VALIDATE '{
"title": "student",
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"age": { "type": ["number", "string"] },
"advisorId": { "type": "number" },
"courses": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseNumber": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"avgGrade": { "type": ["number", "string"] }
},
"required": ["courseNumber", "name", "avgGrade"]
}
},
"dormitory": {
"type": "object",
"properties": {
"dormId": { "type": "number" },
"dormName": { "type": "string", "maxLength": 100 }
},
"required": ["dormId", "dormName"]
}
},
"required": ["studentId", "name", "age", "advisorId", "courses", "dormitory"]
}'
);
-- Create course_tab input table with JSON schema
DROP TABLE IF EXISTS course_tab;
CREATE TABLE course_tab (
data JSON VALIDATE '{
"title": "course",
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"creditHours": { "type": ["number", "string"] },
"students": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["studentId", "name"]
}
},
"teacher": {
"type": "object",
"properties": {
"teacherId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["teacherId", "name"]
},
"Notes": { "type": "string" }
},
"required": ["courseId", "name", "creditHours", "students", "teacher"]
}'
);
-- Create teacher_tab input table with JSON schema
DROP TABLE IF EXISTS teacher_tab;
CREATE TABLE teacher_tab (
data JSON VALIDATE '{
"title": "teacher",
"type": "object",
"properties": {
"_id": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"phoneNumber": {
"type": ["string", "array"]
},
"salary": { "type": "number" },
"department": { "type": "string", "maxLength": 100 },
"coursesTaught": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"classType": { "type": "string", "enum": ["Online", "In-person"] }
},
"required": ["courseId", "name", "classType"]
}
},
"studentsAdvised": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"dormId": { "type": "number" }
},
"required": ["studentId", "name", "dormId"]
}
}
},
"required": ["_id", "name", "phoneNumber", "salary", "department", "coursesTaught", "studentsAdvised"]
}'
);Example 21-24 Generating DDL Schema from JSON Schema Only
In the following SQL code, the converter function
INFER_AND_GENERATE_SCHEMA processes the JSON schema definitions to
generate the required database objects:
DECLARE
ddl_sql CLOB;
BEGIN
ddl_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON ('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB"],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ]}'));
EXECUTE IMMEDIATE ddl_sql;
END;
/
Example 21-25 Generating DDL Output Using INFER_AND_GENERATE_SCHEMA
The following SQL shows the relational schema content generated by
INFER_AND_GENERATE_SCHEMA:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE course_teacher(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
teacher_id number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_id number,
dorm_name varchar2(128),
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_students(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
student_id number,
ora$id_course_root number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(128),
salary number,
department varchar2(128),
phone_number json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
ora$teacher_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_courses(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
avg_grade json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
course_number varchar2(64),
ora$id_student_root number,
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_courses_taught(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
course_id varchar2(64),
class_type varchar2(64),
"_id_teacher_root" number,
ora$teacher_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_students_advised(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_id number,
student_id number,
"_id_teacher_root" number,
ora$teacher_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(128),
notes varchar2(64),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
course_id varchar2(64),
credit_hours json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
ora$id_course_teacher number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
advisor_id number,
student_id number,
ora$id_student_dormitory number,
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'ALTER TABLE course_students
ADD CONSTRAINT fk_course_students_to_course_root FOREIGN KEY (ora$id_course_root) REFERENCES course_root(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_courses
ADD CONSTRAINT fk_student_courses_to_student_root FOREIGN KEY (ora$id_student_root) REFERENCES student_root(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE teacher_courses_taught
ADD CONSTRAINT fk_teacher_courses_taught_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE teacher_students_advised
ADD CONSTRAINT fk_teacher_students_advised_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_course_teacher FOREIGN KEY (ora$id_course_teacher) REFERENCES course_teacher(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory FOREIGN KEY (ora$id_student_dormitory) REFERENCES student_dormitory(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_students_to_course_root_index ON course_students(ora$id_course_root)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_courses_to_student_root_index ON student_courses(ora$id_student_root)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_teacher_courses_taught_to_teacher_root_index ON teacher_courses_taught("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_teacher_students_advised_to_teacher_root_index ON teacher_students_advised("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_course_teacher_index ON course_root(ora$id_course_teacher)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_student_dormitory_index ON student_root(ora$id_student_dormitory)';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
_id : ora$id
studentId: student_id
name
age
advisorId: advisor_id
courses: student_courses @insert @update @delete @array
[{
courseNumber: course_number
name
avgGrade: avg_grade
ora$id
ora$student_flex @flex
}]
dormitory: student_dormitory @insert @update @object
{
dormId: dorm_id
dormName: dorm_name
ora$id
ora$student_flex @flex
}
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
phoneNumber: phone_number
salary
department
coursesTaught: teacher_courses_taught @insert @update @delete @array
[{
courseId: course_id
name
classType: class_type
ora$id
ora$teacher_flex @flex
}]
studentsAdvised: teacher_students_advised @insert @update @delete @array
[{
studentId: student_id
name
dormId: dorm_id
ora$id
ora$teacher_flex @flex
}]
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : ora$id
courseId: course_id
name
creditHours: credit_hours
students: course_students @insert @update @delete @array
[{
studentId: student_id
name
ora$id
ora$course_flex @flex
}]
teacher: course_teacher @insert @update @object
{
teacherId: teacher_id
name
ora$id
ora$course_flex @flex
}
Notes: notes
ora$course_flex @flex
}';
END;
Example 21-26 Data Import and Testing for Relational Schema Defined Without Data
After you use the converter to create a relational schema using the converter, you can then import data into the input tables. The importer then processes the data to populate the duality views:
For this example, we use the three input document sets stored in Oracle Database external tables dataset_student, dataset_teacher and dataset_course as described in this topic:Before Using the Converter (1): Create Database Document Sets
The input documents are shown there, and in a more compact version in this topic:
School Administration Example, Migrator Input Documents
The SQL code is as follows:
-- Verify empty input tables (schema-only conversion)
SELECT json_serialize(data PRETTY) FROM student_tab;
SELECT json_serialize(data PRETTY) FROM teacher_tab;
SELECT json_serialize(data PRETTY) FROM course_tab;
-- Insert data from source datasets
INSERT INTO student_tab SELECT data FROM dataset_student;
INSERT INTO teacher_tab SELECT data FROM dataset_teacher;
INSERT INTO course_tab SELECT data FROM dataset_course;
COMMIT;
-- Create error log tables
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
err_log_table_name => 'COURSE_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
err_log_table_name => 'TEACHER_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
err_log_table_name => 'STUDENT_ERR_LOG',
skip_unsupported => TRUE);
END;
/
-- Import data into duality views using the importer
BEGIN
DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
"errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
END;
/
Import results:
SQL> -- CREATE ERROR LOG TABLES
SQL> BEGIN
2 DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
3 err_log_table_name => 'COURSE_ERR_LOG',
4 skip_unsupported => TRUE);
5 DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
6 err_log_table_name => 'TEACHER_ERR_LOG',
7 skip_unsupported => TRUE);
8 DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
9 err_log_table_name => 'STUDENT_ERR_LOG',
10 skip_unsupported => TRUE);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Import the data in the input tables into the duality views created using import_all method
SQL> BEGIN
2 DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
3 "viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
4 "errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
5 END;
6 /
PL/SQL procedure successfully completed.
Validate the import data and error log data.
-- Verify data after import
SELECT json_serialize(data PRETTY) FROM student;
SELECT json_serialize(data PRETTY) FROM teacher;
SELECT json_serialize(data PRETTY) FROM course;
-- check error logs
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
Verify the log files:
SQL> -- Verify data after import
SQL> SELECT json_serialize(data PRETTY) FROM student;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "F0BB009B61526E8EBD946892BFE652D5",
"asof" : "0000000000331A3D"
},
"studentId" : 1,
"name" : "Donald P.",
"age" : 20,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90,
"ora$id" : 1
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 2
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 3
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 1
}
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "3446D64543E7D02471614A6DBCF256C9",
"asof" : "0000000000331A3D"
},
"studentId" : 2,
"name" : "Elena H.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 4
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 5
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 6
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"ora$id" : 2
}
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "0F91B5AAD26CF62EF879F8CDF590E359",
"asof" : "0000000000331A3D"
},
"studentId" : 3,
"name" : "Francis K.",
"age" : 20,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 7
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"ora$id" : 3
}
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "27AB02546179A05E538E28EDEFE680A9",
"asof" : "0000000000331A3D"
},
"studentId" : 4,
"name" : "Georgia D.",
"age" : 19,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 8
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 9
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 10
}
],
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN",
"ora$id" : 4
}
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "E5EA57B504C820A896706E517E0CB1A7",
"asof" : "0000000000331A3D"
},
"studentId" : 5,
"name" : "Hye E.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90,
"ora$id" : 11
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 12
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 5
}
}
{
"_id" : 6,
"_metadata" :
{
"etag" : "C39E4C365410FD0A6D8C5903C0D86E3B",
"asof" : "0000000000331A3D"
},
"studentId" : 6,
"name" : "Ileana D.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 13
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"ora$id" : 6
}
}
{
"_id" : 7,
"_metadata" :
{
"etag" : "E2751BF24D34E1973695FEDDBCC47BE9",
"asof" : "0000000000331A3D"
},
"studentId" : 7,
"name" : "Jatin S.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 14
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 15
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"ora$id" : 7
}
}
{
"_id" : 8,
"_metadata" :
{
"etag" : "1F113FE52C21DBBDD71B1CA47E48F517",
"asof" : "0000000000331A3D"
},
"studentId" : 8,
"name" : "Katie H.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 16
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 17
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"ora$id" : 8
}
}
{
"_id" : 9,
"_metadata" :
{
"etag" : "515C42A82BAA59A00C4006D6A59FAB33",
"asof" : "0000000000331A3D"
},
"studentId" : 9,
"name" : "Luis F.",
"age" : "Nineteen",
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 18
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 19
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 20
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 9
}
}
{
"_id" : 10,
"_metadata" :
{
"etag" : "E13468279294236B728DA174F6A9720A",
"asof" : "0000000000331A3D"
},
"studentId" : 10,
"name" : "Ming L.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 21
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"ora$id" : 10
}
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"_metadata" :
{
"etag" : "CE2DA3E57BD4172E78D0398350683F68",
"asof" : "0000000000331A43"
},
"name" : "Abdul J.",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"salary" : 200000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH102",
"name" : "Calculus",
"classType" : "In-person",
"ora$id" : 1
},
{
"courseId" : "MATH101",
"name" : "Algebra",
"classType" : "Online",
"ora$id" : 2
}
],
"studentsAdvised" :
[
{
"studentId" : 9,
"name" : "Luis F.",
"dormId" : 201,
"ora$id" : 1
},
{
"studentId" : 4,
"name" : "Georgia D.",
"dormId" : 203,
"ora$id" : 2
},
{
"studentId" : 10,
"name" : "Ming L.",
"dormId" : 202,
"ora$id" : 3
},
{
"studentId" : 7,
"name" : "Jatin S.",
"dormId" : 204,
"ora$id" : 4
}
]
}
{
"_id" : 102,
"_metadata" :
{
"etag" : "55C8549D080C1A7FC683E3E2379D09A8",
"asof" : "0000000000331A43"
},
"name" : "Betty Z.",
"phoneNumber" : "222-555-022",
"salary" : 300000,
"department" : "Computer Science",
"coursesTaught" :
[
{
"courseId" : "CS102",
"name" : "Data Structures",
"classType" : "In-person",
"ora$id" : 3
},
{
"courseId" : "CS101",
"name" : "Algorithms",
"classType" : "Online",
"ora$id" : 4
}
],
"studentsAdvised" :
[
{
"studentId" : 8,
"name" : "Katie H.",
"dormId" : 205,
"ora$id" : 5
},
{
"studentId" : 6,
"name" : "Ileana D.",
"dormId" : 205,
"ora$id" : 6
},
{
"studentId" : 1,
"name" : "Donald P.",
"dormId" : 201,
"ora$id" : 7
}
]
}
{
"_id" : 103,
"_metadata" :
{
"etag" : "90D13738146638E2FC1B016B191C0713",
"asof" : "0000000000331A43"
},
"name" : "Colin J.",
"phoneNumber" :
[
"222-555-023"
],
"salary" : 220000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"classType" : "Online",
"ora$id" : 5
}
],
"studentsAdvised" :
[
{
"studentId" : 5,
"name" : "Hye E.",
"dormId" : 201,
"ora$id" : 8
},
{
"studentId" : 3,
"name" : "Francis K.",
"dormId" : 204,
"ora$id" : 9
},
{
"studentId" : 2,
"name" : "Elena H.",
"dormId" : 202,
"ora$id" : 10
}
]
}
{
"_id" : 104,
"_metadata" :
{
"etag" : "D4D644FB68590D5A00EC53778F0E7226",
"asof" : "0000000000331A43"
},
"name" : "Natalie C.",
"phoneNumber" : "222-555-044",
"salary" : 180000,
"department" : "Computer Science",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "CCB9A27CFA882216E1FD17E01A5AB715",
"asof" : "0000000000331A49"
},
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 5,
"name" : "Hye E.",
"ora$id" : 1
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 2
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J.",
"ora$id" : 1
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "1453FD5A57994916112993C04CC504E7",
"asof" : "0000000000331A49"
},
"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" :
[
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 3
},
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 4
},
{
"studentId" : 10,
"name" : "Ming L.",
"ora$id" : 5
},
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 6
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J.",
"ora$id" : 2
},
"Notes" : null
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "DB4D293C0FBE10479973F4E3C2EC9259",
"asof" : "0000000000331A49"
},
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 7
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 8
},
{
"studentId" : 7,
"name" : "Jatin S.",
"ora$id" : 9
},
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 10
},
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 11
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z.",
"ora$id" : 3
},
"Notes" : null
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "1EF009C45D1C6EE93824AB30B75FA47C",
"asof" : "0000000000331A49"
},
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 12
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 13
},
{
"studentId" : 7,
"name" : "Jatin S.",
"ora$id" : 14
},
{
"studentId" : 8,
"name" : "Katie H.",
"ora$id" : 15
},
{
"studentId" : 5,
"name" : "Hye E.",
"ora$id" : 16
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z.",
"ora$id" : 4
},
"Notes" : null
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "0E6E4127968CB3744D7F3B20A2D2FC4C",
"asof" : "0000000000331A49"
},
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : "3",
"students" :
[
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 17
},
{
"studentId" : 3,
"name" : "Francis K.",
"ora$id" : 18
},
{
"studentId" : 8,
"name" : "Katie H.",
"ora$id" : 19
},
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 20
},
{
"studentId" : 6,
"name" : "Ileana D.",
"ora$id" : 21
}
],
"teacher" :
{
"teacherId" : 103,
"name" : "Colin J.",
"ora$id" : 5
},
"Notes" : null
}
SQL>
SQL> -- check error logs
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
no rows selectedParent topic: Using the Converter With Defined JSON Schema
21.11.4 Example Using a JSON Schema With Input Data to Infer a Relational Schema
We define a JSON schema for the JSON column and insert data into the input
table at the same time. The INFER_AND_GENERATE_SCHEMA function uses both the
schema and the data to create a more accurate and complete relational database
schema.
This example demonstrates creating database input tables by using JSON schema definitions while simultaneously inserting the JSON data before calling the converter functions. The converter uses both the explicit JSON schema constraints and the actual JSON data patterns to generate optimized relational database schemas. Fields defined in the JSON schema and the fields present in the inserted data can vary, so both sources contribute to the final normalized schema.
Example 21-27 Creating Database Input Tables Using Enhanced JSON Schemas
The following SQL code example shows input tables with JSON schemas that include additional fields not present in the original data, demonstrating schema flexibility:
-- Create student_tab input table with JSON schema
-- "Note: The dormLocation field is added to the JSON schema but is not required when importing data from
-- the input table. The dorm_location column will be added to the student_dormitory table after
-- applying the converter function."
DROP TABLE IF EXISTS student_tab;
CREATE TABLE student_tab (
data JSON VALIDATE '{
"title": "student",
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"advisorId": { "type": "number" },
"age": { "type": ["number", "string"] },
"courses": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseNumber": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"avgGrade": { "type": ["number", "string"] }
},
"required": ["courseNumber", "name", "avgGrade"]
}
},
"dormitory": {
"type": "object",
"properties": {
"dormId": { "type": "number" },
"dormName": { "type": "string", "maxLength": 100 },
"dormLocation": { "type": "string", "maxLength": 100 }
},
"required": ["dormId", "dormName"]
}
},
"required": ["studentId", "name", "age", "advisorId", "courses", "dormitory"]
}'
);
-- Create course_tab input table with JSON schema
-- The 'location' field is added to schema and will create location column in course_root table
DROP TABLE IF EXISTS course_tab;
CREATE TABLE course_tab (
data JSON VALIDATE '{
"title": "course",
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"creditHours": { "type": ["number", "string"] },
"location": { "type": "string" },
"students": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["studentId", "name"]
}
},
"teacher": {
"type": "object",
"properties": {
"teacherId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["teacherId", "name"]
},
"Notes": { "type": "string" }
},
"required": ["courseId", "name", "creditHours", "students", "teacher"]
}'
);
-- Create teacher_tab input table with JSON schema
-- emailId field added to schema, phoneNumber omitted from schema but present in data
DROP TABLE IF EXISTS teacher_tab;
CREATE TABLE teacher_tab (
data JSON VALIDATE '{
"title": "teacher",
"type": "object",
"properties": {
"_id": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"emailId": { "type": "string" },
"salary": { "type": "number" },
"department": { "type": "string", "maxLength": 100 },
"coursesTaught": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"classType": { "type": "string", "enum": ["Online", "In-person"] }
},
"required": ["courseId", "name", "classType"]
}
},
"studentsAdvised": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"dormId": { "type": "number" }
},
"required": ["studentId", "name", "dormId"]
}
}
},
"required": ["_id", "name", "salary", "department", "coursesTaught", "studentsAdvised"]
}'
);
Example 21-28 Inserting JSON Data Before Database Schema Generation
In combined JSON schema-data migration, the JSON data is inserted into the database input tables before calling the converter functions:
-- Insert data into input tables before schema generation
INSERT INTO student_tab SELECT data FROM dataset_student;
INSERT INTO teacher_tab SELECT data FROM dataset_teacher;
INSERT INTO course_tab SELECT data FROM dataset_course;
COMMIT;
-- Validate inserted data
SELECT json_serialize(data PRETTY) FROM student_tab;
SELECT json_serialize(data PRETTY) FROM teacher_tab;
SELECT json_serialize(data PRETTY) FROM course_tab;
Example 21-29 Data Validation Output
SQL> SELECT json_serialize(data PRETTY) FROM student_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"studentId" : 1,
"name" : "Donald P.",
"age" : 20,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 2,
"name" : "Elena H.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
}
}
{
"studentId" : 3,
"name" : "Francis K.",
"age" : 20,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
}
}
{
"studentId" : 4,
"name" : "Georgia D.",
"age" : 19,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN"
}
}
{
"studentId" : 5,
"name" : "Hye E.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 6,
"name" : "Ileana D.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
}
}
{
"studentId" : 7,
"name" : "Jatin S.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
}
}
{
"studentId" : 8,
"name" : "Katie H.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
}
}
{
"studentId" : 9,
"name" : "Luis F.",
"age" : "Nineteen",
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 10,
"name" : "Ming L.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
}
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"name" : "Abdul J.",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"salary" : 200000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH101",
"name" : "Algebra",
"classType" : "Online"
},
{
"courseId" : "MATH102",
"name" : "Calculus",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"studentId" : 4,
"name" : "Georgia D.",
"dormId" : 203
},
{
"studentId" : 7,
"name" : "Jatin S.",
"dormId" : 204
},
{
"studentId" : 9,
"name" : "Luis F.",
"dormId" : 201
},
{
"studentId" : 10,
"name" : "Ming L.",
"dormId" : 202
}
]
}
{
"_id" : 102,
"name" : "Betty Z.",
"phoneNumber" : "222-555-022",
"salary" : 300000,
"department" : "Computer Science",
"coursesTaught" :
[
{
"courseId" : "CS101",
"name" : "Algorithms",
"classType" : "Online"
},
{
"courseId" : "CS102",
"name" : "Data Structures",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"studentId" : 1,
"name" : "Donald P.",
"dormId" : 201
},
{
"studentId" : 6,
"name" : "Ileana D.",
"dormId" : 205
},
{
"studentId" : 8,
"name" : "Katie H.",
"dormId" : 205
}
]
}
{
"_id" : 103,
"name" : "Colin J.",
"phoneNumber" :
[
"222-555-023"
],
"salary" : 220000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"classType" : "Online"
}
],
"studentsAdvised" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"dormId" : 202
},
{
"studentId" : 3,
"name" : "Francis K.",
"dormId" : 204
},
{
"studentId" : 5,
"name" : "Hye E.",
"dormId" : 201
}
]
}
{
"_id" : 104,
"name" : "Natalie C.",
"phoneNumber" : "222-555-044",
"salary" : 180000,
"department" : "Computer Science",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 5,
"name" : "Hye E."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 10,
"name" : "Ming L."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
}
}
{
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 9,
"name" : "Luis F."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 5,
"name" : "Hye E."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 8,
"name" : "Katie H."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : "3",
"students" :
[
{
"studentId" : 3,
"name" : "Francis K."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 6,
"name" : "Ileana D."
},
{
"studentId" : 8,
"name" : "Katie H."
},
{
"studentId" : 9,
"name" : "Luis F."
}
],
"teacher" :
{
"teacherId" : 103,
"name" : "Colin J."
}
}Example 21-30 Generating DDL Output Schema from Combined JSON Schema and Data Sources Using INFER_AND_GENERATE_SCHEMA
The converter function processes both the JSON schema definitions and the inserted JSON data to generate optimized database objects:
DECLARE
ddl_sql CLOB;
BEGIN
ddl_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON ('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB"],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ]}'));
EXECUTE IMMEDIATE ddl_sql;
END;
/
The generated DDL output is as follows:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE course_teacher(
name varchar2(128) /* UNIQUE */,
teacher_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$course_flex JSON(Object),
PRIMARY KEY(teacher_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE map_course_root_to_student_root(
map_course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
map_student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(map_course_id,map_student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
dorm_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_name varchar2(128) /* UNIQUE */,
dorm_location varchar2(128),
ora$student_flex JSON(Object),
PRIMARY KEY(dorm_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
name varchar2(128) /* UNIQUE */,
dorm_id number,
advisor_id number,
student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$teacher_flex JSON(Object),
PRIMARY KEY(student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(128) /* UNIQUE */,
salary number /* UNIQUE */,
email_id varchar2(64),
department varchar2(128),
phone_number json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
ora$teacher_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(128) /* UNIQUE */,
notes varchar2(64),
location varchar2(64),
avg_grade json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
class_type varchar2(64),
credit_hours json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
"_id_teacher_root" number,
teacher_id_course_teacher number,
ora$teacher_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(course_id)
)';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_course_root FOREIGN KEY (map_course_id) REFERENCES course_root(course_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_student_root FOREIGN KEY (map_student_id) REFERENCES student_root(student_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_teacher_root FOREIGN KEY (advisor_id) REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory FOREIGN KEY (dorm_id) REFERENCES student_dormitory(dorm_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_course_teacher FOREIGN KEY (teacher_id_course_teacher) REFERENCES course_teacher(teacher_id) DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_course_root_index ON map_course_root_to_student_root(map_course_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_student_root_index ON map_course_root_to_student_root(map_student_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_teacher_root_index ON student_root(advisor_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_student_dormitory_index ON student_root(dorm_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_teacher_root_index ON course_root("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_course_teacher_index ON course_root(teacher_id_course_teacher)';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
_id : student_id
age
name
courses: map_course_root_to_student_root @insert @update @delete @array
[{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$student_flex @flex
course_root @unnest @insert @update @object
{
name
avgGrade: avg_grade
courseNumber: course_id
}
}]
advisorId:advisor_id
dormitory: student_dormitory @insert @update @object
{
dormId: dorm_id
dormName: dorm_name
dormLocation: dorm_location
ora$student_flex @flex
}
studentId @generated (path: "$._id")
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
salary
emailId: email_id
department
phoneNumber: phone_number
coursesTaught: course_root @insert @update @delete @array
[{
name
courseId: course_id
classType: class_type
ora$teacher_flex @flex
}]
studentsAdvised: student_root @insert @update @delete @array
[{
name
dormId:dorm_id
studentId: student_id
ora$teacher_flex @flex
}]
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
Notes: notes
teacher: course_teacher @insert @update @object
{
name
teacherId: teacher_id
ora$course_flex @flex
}
courseId @generated (path: "$._id")
location
students: map_course_root_to_student_root @insert @update @delete @array
[{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$course_flex @flex
student_root @unnest @insert @update @object
{
name
studentId: student_id
}
}]
creditHours: credit_hours
ora$course_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_STUDENT
BEFORE INSERT
ON STUDENT
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''studentId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_COURSE
BEFORE INSERT
ON COURSE
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''courseId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
END;
Example 21-31 Data Import into Duality Views
After database schema generation, use SQL to import the data:
-- CREATE ERROR LOG TABLES
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
err_log_table_name => 'COURSE_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
err_log_table_name => 'TEACHER_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
err_log_table_name => 'STUDENT_ERR_LOG',
skip_unsupported => TRUE);
END;
/
-- Import the data in the input tables into the duality views created using import_all method
BEGIN
DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
"errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
END;
/
-- Verify data after import
SELECT json_serialize(data PRETTY) FROM student;
SELECT json_serialize(data PRETTY) FROM teacher;
SELECT json_serialize(data PRETTY) FROM course;
-- check error logs
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
Check the log file:
SQL> -- CREATE ERROR LOG TABLES
SQL> BEGIN
2 DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
3 err_log_table_name => 'COURSE_ERR_LOG',
4 skip_unsupported => TRUE);
5 DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
6 err_log_table_name => 'TEACHER_ERR_LOG',
7 skip_unsupported => TRUE);
8 DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
9 err_log_table_name => 'STUDENT_ERR_LOG',
10 skip_unsupported => TRUE);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Import the data in the input tables into the duality views created using import_all method
SQL> BEGIN
2 DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
3 "viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
4 "errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
5 END;
6 /
PL/SQL procedure successfully completed.
Example 21-32 Verify data after import
Check the error log tables to verify the data after import.
SQL> -- Verify data after import
SQL> SELECT json_serialize(data PRETTY) FROM student;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "D59BBECDEF2CB781DDDF5843CD4686DA",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Donald P.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 1
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "8CF3F43FAFFDFEDA524CB6DBCA0FB90F",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Elena H.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 2,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 2,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 2,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"dormLocation" : null
},
"studentId" : 2
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "533366851CC9508AFA08C394F3616972",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Francis K.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 3,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"dormLocation" : null
},
"studentId" : 3
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "693C9DDBAEE3FD99A6967ADBF713BA1A",
"asof" : "000000000033126E"
},
"age" : 19,
"name" : "Georgia D.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 4,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 4,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 4,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN",
"dormLocation" : null
},
"studentId" : 4
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "E923DE9EFD2F19338A2F0657B061AF22",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Hye E.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 5,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 5,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 5
}
{
"_id" : 6,
"_metadata" :
{
"etag" : "992F54118924F34D2F58F7056C093280",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Ileana D.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 6,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"dormLocation" : null
},
"studentId" : 6
}
{
"_id" : 7,
"_metadata" :
{
"etag" : "A34FBC7FCAE6B060B987C0F37B184CE6",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Jatin S.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 7,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 7,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"dormLocation" : null
},
"studentId" : 7
}
{
"_id" : 8,
"_metadata" :
{
"etag" : "4D120806D5BBA8A11B06BE3303E0C30E",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Katie H.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 8,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 8,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"dormLocation" : null
},
"studentId" : 8
}
{
"_id" : 9,
"_metadata" :
{
"etag" : "B43B57898F7E95F93A2A8CD878CD17B3",
"asof" : "000000000033126E"
},
"age" : "Nineteen",
"name" : "Luis F.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 9,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 9,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 9,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 9
}
{
"_id" : 10,
"_metadata" :
{
"etag" : "6E74518D9B67EFF7D3E0162256CEFF4C",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Ming L.",
"courses" :
[
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 10,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"dormLocation" : null
},
"studentId" : 10
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"_metadata" :
{
"etag" : "4E1D8E180D9FE3E418FB3765D87A80F4",
"asof" : "0000000000331274"
},
"name" : "Abdul J.",
"salary" : 200000,
"emailId" : null,
"department" : "Mathematics",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"coursesTaught" :
[
{
"name" : "Algebra",
"courseId" : "MATH101",
"classType" : "Online"
},
{
"name" : "Calculus",
"courseId" : "MATH102",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"name" : "Georgia D.",
"dormId" : 203,
"studentId" : 4
},
{
"name" : "Jatin S.",
"dormId" : 204,
"studentId" : 7
},
{
"name" : "Luis F.",
"dormId" : 201,
"studentId" : 9
},
{
"name" : "Ming L.",
"dormId" : 202,
"studentId" : 10
}
]
}
{
"_id" : 102,
"_metadata" :
{
"etag" : "20BDE221E92B567A11E1652005B76B76",
"asof" : "0000000000331274"
},
"name" : "Betty Z.",
"salary" : 300000,
"emailId" : null,
"department" : "Computer Science",
"phoneNumber" : "222-555-022",
"coursesTaught" :
[
{
"name" : "Algorithms",
"courseId" : "CS101",
"classType" : "Online"
},
{
"name" : "Data Structures",
"courseId" : "CS102",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"name" : "Donald P.",
"dormId" : 201,
"studentId" : 1
},
{
"name" : "Ileana D.",
"dormId" : 205,
"studentId" : 6
},
{
"name" : "Katie H.",
"dormId" : 205,
"studentId" : 8
}
]
}
{
"_id" : 103,
"_metadata" :
{
"etag" : "B7DB096CFD8939D2C5339F0FF43C07B5",
"asof" : "0000000000331274"
},
"name" : "Colin J.",
"salary" : 220000,
"emailId" : null,
"department" : "Mathematics",
"phoneNumber" :
[
"222-555-023"
],
"coursesTaught" :
[
{
"name" : "Advanced Algebra",
"courseId" : "MATH103",
"classType" : "Online"
}
],
"studentsAdvised" :
[
{
"name" : "Elena H.",
"dormId" : 202,
"studentId" : 2
},
{
"name" : "Francis K.",
"dormId" : 204,
"studentId" : 3
},
{
"name" : "Hye E.",
"dormId" : 201,
"studentId" : 5
}
]
}
{
"_id" : 104,
"_metadata" :
{
"etag" : "F82846A780D35515745D14B0ACF372E9",
"asof" : "0000000000331274"
},
"name" : "Natalie C.",
"salary" : 180000,
"emailId" : null,
"department" : "Computer Science",
"phoneNumber" : "222-555-044",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : "CS101",
"_metadata" :
{
"etag" : "36C477EBFE254EF8C668FB1CEE298AEC",
"asof" : "0000000000331274"
},
"name" : "Algorithms",
"Notes" : null,
"teacher" :
{
"name" : "Betty Z.",
"teacherId" : 102
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 7,
"name" : "Jatin S.",
"studentId" : 7
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
}
],
"creditHours" : 5,
"courseId" : "CS101"
}
{
"_id" : "CS102",
"_metadata" :
{
"etag" : "C521E7258A6786CFCC67A550D4FDA3BF",
"asof" : "0000000000331274"
},
"name" : "Data Structures",
"Notes" : null,
"teacher" :
{
"name" : "Betty Z.",
"teacherId" : 102
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 5,
"name" : "Hye E.",
"studentId" : 5
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 7,
"name" : "Jatin S.",
"studentId" : 7
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 8,
"name" : "Katie H.",
"studentId" : 8
}
],
"creditHours" : 3,
"courseId" : "CS102"
}
{
"_id" : "MATH101",
"_metadata" :
{
"etag" : "EADFDF3C9F43773C23C14E391D384C44",
"asof" : "0000000000331274"
},
"name" : "Algebra",
"Notes" : "Prerequisite for Advanced Algebra",
"teacher" :
{
"name" : "Abdul J.",
"teacherId" : 101
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 5,
"name" : "Hye E.",
"studentId" : 5
}
],
"creditHours" : 3,
"courseId" : "MATH101"
}
{
"_id" : "MATH102",
"_metadata" :
{
"etag" : "0E6F52978EFBF49B251C1E442B5019B4",
"asof" : "0000000000331274"
},
"name" : "Calculus",
"Notes" : null,
"teacher" :
{
"name" : "Abdul J.",
"teacherId" : 101
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 10,
"name" : "Ming L.",
"studentId" : 10
}
],
"creditHours" : 4,
"courseId" : "MATH102"
}
{
"_id" : "MATH103",
"_metadata" :
{
"etag" : "3E8CC5863A4A794F4547AD5BF22D6628",
"asof" : "0000000000331274"
},
"name" : "Advanced Algebra",
"Notes" : null,
"teacher" :
{
"name" : "Colin J.",
"teacherId" : 103
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 3,
"name" : "Francis K.",
"studentId" : 3
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 6,
"name" : "Ileana D.",
"studentId" : 6
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 8,
"name" : "Katie H.",
"studentId" : 8
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
}
],
"creditHours" : "3",
"courseId" : "MATH103"
}
SQL>
SQL> -- check error logs
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
no rows selected
Parent topic: Using the Converter With Defined JSON Schema