Obsessed with Oracle PL/SQL

So far in my series on object-oriented development in Oracle Database, all manipulation of object type instances have taken place in PL/SQL. But as you may have guessed from the fact that you "CREATE OR REPLACE" object types, those types are also available for us in SQL. You can create relational tables of object types, called object tables. You can also define columns of relational tables whose datatypes are object types. In this post, I will explore both of these approaches. All the code you see below may be found in this  LiveSQL script , so you can get to know these features by playing around with them yourself. Object Tables It's easy to create object tables and work with the instances in those tables (both selecting and changing rows of data). Here's a simple example: CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE food_table OF food_ot (CONSTRAINT food_ta...

We are using Zoom to host the webcasts for our AskTOM Office Hours program. We schedule the meetings automatically, using their API. We can then also retrieve the meeting information as JSON documents through that same API. Blaine Carter , the Developer Advocate who did all the heavy lifting around the Zoom API, suggested we take a daily snapshot of all our meetings, so that in case anything goes wrong, we can check back in time, grab the meeting ID, and still get that session going. Great idea! He also suggested that I use JSON_TABLE to get the job done. Another great idea! JSON_TABLE , introduced in 12.1, "enables the creation of an inline relational view of JSON content. The JSON_TABLE operator uses a set of JSON path expressions to map content from a JSON document into columns in the view. Once the contents of the JSON document have been exposed as columns, all of the power of SQL can be brought to bear on the content of JSON document." (quoting product manager Mark...