Obsessed with Oracle PL/SQL

On the one hand, when you test something, you want to keep your test code as simple as possible so that you can focus on the issue you are testing. On the other hand, if you make your code too  simple you might find yourself baffled at the resulting behavior. Why? Because the PL/SQL compiler is just too darned smart. Today, I got a DM on Twitter asking me why the package body below was compiling without any errors, even though he specified that the PLW-06009 warning should be treated as a compile error. The code: ALTER SESSION SET plsql_warnings = 'Error:6009'; CREATE OR REPLACE PACKAGE pkg_test AS PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2); END pkg_test; / CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2) IS BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END test_job; END pkg_test; / Certainly seems like that exception handler allows the OTHERS handler to exit test_job with...

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

The Oracle AskTOM website is one of the go-to forums to get your questions answered on Oracle Database technology. For many years, it was managed singlehandedly by the legendary Tom Kyte. Since retirement in 2015, questions have answered by an expanded answer team of Chris Saxon , Connor McDonald and Maria Colgan . Chris and Connor are members of my Oracle Developer Advocates team , which also maintains and enhances the AskTOM site. And we sure have been enhancing! In fact, we just released version 4 of the site, which adds a whole new, exciting program: Office Hours. Office Hours offers scheduled, live Q&A sessions with a wide variety of Oracle Database  experts , from product managers to evangelists and even developers.  All ready, willing and able to help you get the best out of Oracle technology.  And the best part: AskTOM Office Hours sessions are 100% free!   AskTOM Office Hours continues the pioneering tradition of Ask TOM. Launched in 2...

2017 is no more. Which means that there are no more quizzes to be taken in 2017. Which means that we can now compute the rankings for the weekly PL/SQL tournament at the Dev Gym - and get ready for the 2017 championship event! The top fifty-ranked players from 2017 will be invited to participate in the PL/SQL Challenge Championship for 2017, which will take place in March 2018 (date still to be finalized). The number in parentheses after their names are the number of championships in which they have already participated. As you can see, many of these players are incredibly dedicated to their craft as Oracle Database Developers generally and to their standing on the Dev Gym in particular. Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! By the way, if you are not yet taking our weekly tournament quizzes at the Dev Gym, give it a try. You do not have to play competitively; you can opt-out of rankings.  Heck, you can ...