Don't test PL/SQL features with trivial code

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 without executing…

Using JSON_TABLE to move JSON data to a relational table

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.2, "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 Drake fr…

JSON and PL/SQL 12.2: Get values from JSON documents with API

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:


Oracle AskTOM Office Hours: free, live Q&A sessions with Oracle experts!

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 2000 by Tom Kyte, the site now has a dedicated tea…

Fifty top PL/SQL players heading to the Dev Gym Championship!

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 even play inc…

Implicit vs Explicit Cursor: Which should you use?

My post on Conditional Logic in PL/SQL generated some interesting discussion concerning whether to use an implicit cursor (SELECT-INTO) or an explicit cursor (OPEN-FETCH-CLOSE) when fetching a single row of data.

"Hmmm," you might be saying to yourself, "a conversation about SQL queries in a post on conditional logic? That's strange."

Ah, but maybe not so strange. Because as one person put it in the comments:

Yes, this is true. You can use exception handling as a kind of conditional branching logic. But should you? Generally, no. It's a good idea to keep application logic out of exception sections. Developers don't expect or look for business logic in those sections. They are, after all, for dealing with exceptions - errors. So if you put lots of code in EXCEPTION WHEN clauses, they can be easily overlooked, making your code harder to debug and enhance.

But that question - and the recommendation to avoid the exception section - then led to other comment…

PL/SQL 101: Nulls in PL/SQL

The Oracle Database supports a concept of a null value, which means, essentially, that it has no value. The idea of having nulls in a relational database is controversial, but Oracle Database supports them and you need to know how they can impact your work in PL/SQL.

First, and most important, remember that:
Null is never equal to anything else, including null. And certainly 0.
Null is never not equal to anything else, including null.DECLARE var INTEGER; BEGIN IF var = NULL THEN ... IF NULL = NULL THEN ... IF var <> NULL THEN ... IF NULL != NULL THEN ... END;
you can rest assured that the code represented by "..." will never be executed.

Note: NULL in the above code is a literal value with a non-value of null.

The same holds true for where clause predicates in a SQL statement. The following queries will never return any rows, regardless of the contents of the employees table.

SELECT * FROM employees WHERE employee_id = NULL / SELECT * FROM employe…