Mining Application Express data dictionary views: find unconditional processes

I ran into a problem yesterday on the Oracle Dev Gym (offering quizzes, workouts and classes on Oracle technologies). A number of rows of data were incorrectly deleted. I was able to use Flashback Query to restore them (thank you, thank you, Flashback Query!). Crisis averted. 
But how did this come about?

I recruited Chris Saxon to help me figure out how this could have happened. In relatively short order, we narrowed down the culprit to a process in the Dev Gym Application Express definition that was unconditionally "removing previews" - but was in fact removing all rows, "previews" or not. Ugh.

So we fixed that.

But it got me wondering and worrying: what other processes in my app are unconditional? And should they be?

While some processes fire unconditionally on a page (for example, to get the data from tables and display them on the screen), many are (or should be!) restricted to a button press, the result of a conditional expression, or an authorization scheme…

An appreciation of UI developers from a database developer

From what I can tell, JavaScript developers write much more complicated code, to handle much more challenging requirements, than I do, with my SQL and PL/SQL programming in the Oracle Database.

I am quite certain that JavaScript developers feel this sentiment even more strongly. People like me (veterans of multiple decades of database-centric application development) are seen as dinosaurs, using ancient, uncool technologies.

The things we do seem distant, unimportant, even simplistic, compared to the tough stuff they deal with on a daily basis: asynchronous! streams! containers! microservices! promises!....and so forth.

Heck, I haven't even had to change my "framework" for years and years! :-)

But here's something we should all of us keep in mind:
Sometimes relatively simple tasks can also be critical tasks,
in which case simplicity becomes a significant advantage (a feature, not a bug).
Now, don't get me wrong. I am not saying that I think the task of UI develope…

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…