Session 01

View All Public Scripts Login to Run Script

Script Name Session 01
Visibility Unlisted - anyone with the share link can access
Description Validating JSON functionality
Contributor pawangera@gmail.com
Created Friday February 08, 2019

Statement 1

CREATE TABLE R1_TESTJSON 
   (TestJSON CLOB 
    CONSTRAINT ensure_json CHECK (TestJSON IS JSON))

Table created.

Statement 2

declare 
 
vClob clob; 
 
begin 
 
vClob := '{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}'; 
 
insert into R1_TESTJSON values(vClob); 
 
end;

Statement processed.

Statement 3

CREATE TABLE R1_TESTJSON 
   (TestJSON CLOB 
    CONSTRAINT ensure_json CHECK (TestJSON IS JSON))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 4

declare 
 
vClob clob; 
 
begin 
 
vClob := '{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}'; 
 
insert into R1_TESTJSON values(vClob); 
 
end; 
 
 
 
select getJSON.TestJSON from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101';

ORA-06550: line 487, column 1:
PLS-00103: Encountered the symbol "SELECT"

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 5

select getJSON.TestJSON from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101'


TESTJSON
{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}

TESTJSON
{ "Test": "121.00.101", "allowedtables": [{ "name": "t", "attributes": { "l": "A" }, "groups": [{ "name": "grp", "attributes": { "n": "AI" }, "categories": [{ "name": "cat", "attributes": { "n": "50" }, "columns": [{ "name": "col", "attributes": { "n": "AO" }, "values": [{ "value": "col_value", "attributes": null, "data": "536" }] }] }, { "name": "cat", "attributes": { "n": "60" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "70" }, "columns": [{ "name": "col", "attributes": { "n": "DT" }, "values": [{ "value": "col_value", "attributes": null, "data": "2017-12-31" }] }] }] }, { "name": "grp", "attributes": { "n": "CL" }, "categories": [{ "name": "cat", "attributes": { "n": "100" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "110" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "120" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "0" }] }] }, { "name": "cat", "attributes": { "n": "130" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "140" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "150" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "160" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "170" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "180" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "190" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "2" }] }] }, { "name": "cat", "attributes": { "n": "200" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }] }, { "name": "grp", "attributes": { "n": "ID" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "LO" }, "categories": [{ "name": "cat", "attributes": { "n": "20" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "43" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "27" }] }] }, { "name": "cat", "attributes": { "n": "30" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "16" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "42" }] }] }, { "name": "cat", "attributes": { "n": "40" }, "columns": [{ "name": "col", "attributes": { "n": "N" }, "values": [{ "value": "col_value", "attributes": null, "data": "HR035" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }] }] }

Statement 6

select getJSON.TestJSON.allowedtables from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101'


ALLOWEDTABLES
 -

ALLOWEDTABLES
-

Statement 7

select getJSON.TestJSON from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101'


TESTJSON
{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}

TESTJSON
{ "Test": "121.00.101", "allowedtables": [{ "name": "t", "attributes": { "l": "A" }, "groups": [{ "name": "grp", "attributes": { "n": "AI" }, "categories": [{ "name": "cat", "attributes": { "n": "50" }, "columns": [{ "name": "col", "attributes": { "n": "AO" }, "values": [{ "value": "col_value", "attributes": null, "data": "536" }] }] }, { "name": "cat", "attributes": { "n": "60" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "70" }, "columns": [{ "name": "col", "attributes": { "n": "DT" }, "values": [{ "value": "col_value", "attributes": null, "data": "2017-12-31" }] }] }] }, { "name": "grp", "attributes": { "n": "CL" }, "categories": [{ "name": "cat", "attributes": { "n": "100" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "110" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "120" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "0" }] }] }, { "name": "cat", "attributes": { "n": "130" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "140" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "150" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "160" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "170" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "180" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "190" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "2" }] }] }, { "name": "cat", "attributes": { "n": "200" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }] }, { "name": "grp", "attributes": { "n": "ID" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "LO" }, "categories": [{ "name": "cat", "attributes": { "n": "20" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "43" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "27" }] }] }, { "name": "cat", "attributes": { "n": "30" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "16" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "42" }] }] }, { "name": "cat", "attributes": { "n": "40" }, "columns": [{ "name": "col", "attributes": { "n": "N" }, "values": [{ "value": "col_value", "attributes": null, "data": "HR035" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }] }] }

Statement 8

CREATE TABLE R1_TESTJSON 
   (TestJSON CLOB 
    CONSTRAINT ensure_json CHECK (TestJSON IS JSON))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 9

declare 
 
vClob clob; 
 
begin 
 
vClob := '{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}'; 
 
insert into R1_TESTJSON values(vClob); 
 
end; 
 
 
 
select getJSON.TestJSON from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101';

ORA-06550: line 487, column 1:
PLS-00103: Encountered the symbol "SELECT"

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 10

select getJSON.TestJSON from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101'


TESTJSON
{ 
	"Test": "121.00.101", 
	"allowedtables": [{ 
		"name": "t", 
		"attributes": { 
			"l": "A" 
		}, 
		"groups": [{ 
			"name": "grp", 
			"attributes": { 
				"n": "AI" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "50" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "AO" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "536" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "60" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "70" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DT" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2017-12-31" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "CL" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "100" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "110" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "120" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "0" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "130" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "140" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "150" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "160" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "3" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "170" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "180" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "190" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "2" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "200" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "C" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "1" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "ID" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "LO" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "20" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "43" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "27" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "30" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "DG" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "16" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "MI" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "42" 
					}] 
				}] 
			}, 
			{ 
				"name": "cat", 
				"attributes": { 
					"n": "40" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "N" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "HR035" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}, 
		{ 
			"name": "grp", 
			"attributes": { 
				"n": "Test" 
			}, 
			"categories": [{ 
				"name": "cat", 
				"attributes": { 
					"n": "10" 
				}, 
				"columns": [{ 
					"name": "col", 
					"attributes": { 
						"n": "H" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "102" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "R" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "861" 
					}] 
				}, 
				{ 
					"name": "col", 
					"attributes": { 
						"n": "S" 
					}, 
					"values": [{ 
						"value": "col_value", 
						"attributes": null, 
						"data": "17" 
					}] 
				}] 
			}] 
		}] 
	}] 
}

TESTJSON
{ "Test": "121.00.101", "allowedtables": [{ "name": "t", "attributes": { "l": "A" }, "groups": [{ "name": "grp", "attributes": { "n": "AI" }, "categories": [{ "name": "cat", "attributes": { "n": "50" }, "columns": [{ "name": "col", "attributes": { "n": "AO" }, "values": [{ "value": "col_value", "attributes": null, "data": "536" }] }] }, { "name": "cat", "attributes": { "n": "60" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "70" }, "columns": [{ "name": "col", "attributes": { "n": "DT" }, "values": [{ "value": "col_value", "attributes": null, "data": "2017-12-31" }] }] }] }, { "name": "grp", "attributes": { "n": "CL" }, "categories": [{ "name": "cat", "attributes": { "n": "100" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "110" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "120" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "0" }] }] }, { "name": "cat", "attributes": { "n": "130" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "140" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "150" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "160" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "3" }] }] }, { "name": "cat", "attributes": { "n": "170" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "180" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }, { "name": "cat", "attributes": { "n": "190" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "2" }] }] }, { "name": "cat", "attributes": { "n": "200" }, "columns": [{ "name": "col", "attributes": { "n": "C" }, "values": [{ "value": "col_value", "attributes": null, "data": "1" }] }] }] }, { "name": "grp", "attributes": { "n": "ID" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "LO" }, "categories": [{ "name": "cat", "attributes": { "n": "20" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "43" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "27" }] }] }, { "name": "cat", "attributes": { "n": "30" }, "columns": [{ "name": "col", "attributes": { "n": "DG" }, "values": [{ "value": "col_value", "attributes": null, "data": "16" }] }, { "name": "col", "attributes": { "n": "MI" }, "values": [{ "value": "col_value", "attributes": null, "data": "42" }] }] }, { "name": "cat", "attributes": { "n": "40" }, "columns": [{ "name": "col", "attributes": { "n": "N" }, "values": [{ "value": "col_value", "attributes": null, "data": "HR035" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }, { "name": "grp", "attributes": { "n": "Test" }, "categories": [{ "name": "cat", "attributes": { "n": "10" }, "columns": [{ "name": "col", "attributes": { "n": "H" }, "values": [{ "value": "col_value", "attributes": null, "data": "102" }] }, { "name": "col", "attributes": { "n": "R" }, "values": [{ "value": "col_value", "attributes": null, "data": "861" }] }, { "name": "col", "attributes": { "n": "S" }, "values": [{ "value": "col_value", "attributes": null, "data": "17" }] }] }] }] }] }

Statement 11

select getJSON.TestJSON.allowedtables from R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101'


ALLOWEDTABLES
 -

ALLOWEDTABLES
-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database