Obsessed with Oracle PL/SQL

What does soda have to do with PL/SQL and Oracle Database? Not much...but SODA. Ah, there we have a different story to tell. SODA stands for "Simple Oracle Document Access." It's a set of NoSQL-style APIs that let you create and store collections of documents (most importantly JSON) in Oracle Database, retrieve them, and query them, without needing to know SQL or how the documents are stored in the database. Read lots more about SODA here . As of Oracle Database 18c, we offer SODA APIs for Java , C ,   Node.js (JavaScript), Python ,  REST and PL/SQL . I published an article on SODA for PL/SQL in Oracle Magazine; in this blog post, I focus on some highlights. Please do read the full article (and others still to come!). Also, Tim Hall of Oracle-BASE offers his usual outstanding treatment of this topic  here . SODA for PL/SQL? Whatever for? First and most important, why would a database developer who writes PL/SQL want to avoid  SQL and pretend that t...

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value gr...

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...

With the release of Oracle Database 12c,  Oracle SQL entered the Age of JSON. You can use SQL to join JSON data with relational data. You can extract content from within the JSON document, and make it available for relational processes and tools. You can even query, right from within the database, JSON data that is stored outside Oracle Database in an external table. Check out my post on JSON resources for more guidance on all of the above. And that was all possible in 12.1. In Oracle Database 12c Release 2, we added several pre-defined PL/SQL object types to perform fine-grained programmatic construction and manipulation of in-memory JSON data. I'll publish occasional posts on what you can do with these types. Here's the first one, showing you how to get the value for a specific key in a JSON document. Suppose I've created and populated my species table as follows: CREATE TABLE json_species ( id NUMBER NOT NULL PRIMARY KEY, info CLOB CONSTRAINT is_js...

I am (finally) diving in to explore and then write about using JSON in Oracle Database 12c - with SQL (Releases 1 and 2) and - more to the point for me - with PL/SQL (based on enhancements in Release 2). Expect to see posts on this blog, articles in Oracle Magazine , quizzes and workouts at the Oracle Dev Gym , and more, in 2018. To that end, I have been collecting links to useful resources on this topic, and I realized that you might benefit from that list just as much as I will. So why not share it now? :-) Before exploring Oracle-specific resources, visit json.org to learn more about JSON itself. Oracle Documentation Let's start with the official source of truth: our doc set. First and foremost, we have the  Database JSON Developer's Guide , which covers how to store, generate, view, manipulate, manage, search, and query JSON. And here are some specific links you might find handy: Basic SQL/JSON Path Expression Syntax The basic syntax of a SQL/JSON p...

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL . It's very cool and a lot of fun to put together. Hopefully it will be way more  fun for you to see if you can solve our challenges. Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality. Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out. First, though, if you haven't done anything  with JSON yet, start by looking over these: JSON in Oracle Database JSON Support in Oracle Database 12c Release 1 (12.1.0.2) Plus, all the code shown below can be seen, and run, in LiveSQL ...