Showing posts from 2018

Tips for a great presentation

There's no shortage of people giving advice on how to improve your presentation skills and impact. I offer a short list of links at the bottom of this post. 

I though I'd take a few moments to share some tips I follow to help me make the most of my time in front of audiences.

Why listen to me? I've been doing talks on the PL/SQL language since 1992 and I am pretty sure that only 3 members of all those audiences ever fell asleep during my talk.

What are the (at most) three key takeaways?

Most attendees will forget most of what you said soon after leaving the session. Certainly almost every single technical detail will be lost. So you need to decide before you start your talk what  are the at most three things you want an attendee to remember.

Then put those in a slide and tell them right at a start.

Remind them during your talk when you are getting to one of those top 3 things.

Use the slide again at the end of your talk to drive the points home.

I also find it helpful to remi…

Nested blocks, autonomous transactions and "Where do I commit?"

This question rolled into my In Box today:
If I have a procedure that is AUTONOMOUS_TRANSACTION that does an insert and then it calls a procedure with an insert, does the second procedure need a commit, or will the procedure with the AUTONOMOUS_TRANSACTION handle the commit? If you don't know off the top of your head, don't worry, I can build a test. First of all, if you ever find yourself writing something like "If you don't know off the top of your head, don't worry, I can build a test." then please by all means go right ahead and build yourself a test script.

By doing so, you will better understand the feature in question and remember what you learned. Plus you end up with a script you can share with the community on LiveSQL.

But I don't mind answering such questions. That way I get to better understand the feature in question, remember what I learned, share a script on LiveSQL (link at bottom of post), and also add to my blog. :-)

So here goes: the ans…

A new name - and amazing new future - for PL/SQL

[You might think that this was published on April 2nd, but in fact it was published on April 1st.]

PL/SQL, the database programming language from Oracle, introduced in 1991 and used by millions over the years to implement data APIs and business logic in mission critical applications from which billions of humans benefit daily, is undergoing a radical transformation in order to stay relevant for, and meta-cool to, future generations of developers.

After a careful examination of all modern programming languages and the definitive StackOverflow developer surveys, the PL/SQL development team implemented a super-secret plan (yes, that’s correct, even the Distinguished Product Manager for PL/SQL, Bryn Llewellyn, is unaware of what you are about to read. So don’t bother him about it, OK?).

I am, therefore, inordinately pleased and honored to be the first to announce the following changes for PL/SQL in Oracle Database 20c:
PL/SQL will now be a case-insensitive language. Sort of.Only lower-case…

Rankings for 2017 PL/SQL Championship on the Oracle Dev Gym

Thirty-six Oracle Database technologists competed on March 22nd in the 2017 PL/SQL Annual Championship at the Oracle Dev Gym. With five tough quizzes by yours truly, the competition was fierce! Congratulations first and foremost to our top-ranked players:

1st Place: li_bao of Russia
2nd Place: mentzel.iudith of Israel
3rd Place: NielsHecker of Germany

Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational.

Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community.

In the table below of results for this championship, the number next to the player's name is the number of times that player has participated in a championship. Below that table, you will find another list showing the championship history of each of these players.

RankNameTotal Time% CorrectTotal Score1li_bao (4)27 m78%55922mentzel.iudith (4)44 m78%55203NielsHecker (4)43 m76%53744Oleks…

Qualified expressions (aka, constructor functions) for collections and records in 18c

As anyone who has followed me over the years knows, I like the Oracle PL/SQL language. Sure, it's not the newest, coolest kid on the block (it probably never was). But then, either am I. :-) PL/SQL is, on the other hand, a delightfully straightforward, easy to learn and write language that serves its purpose well: implement APIs to data (SQL) and business logic, right inside the database.

To serve that purpose, of course, PL/SQL needs to support lots of "big ticket" functionality: super-smooth and easy native dynamic SQL, canonicalization of static SQL to minimize the need for hard-parsing, invoker rights (AUTHID CURRENT_USER) and so much more.

But I must confess: the features of PL/SQL that I love the best are the relatively "little" things that make it easy for me to be productive as I churn out the packages (and, yes, I still do write lots of PL/SQL code, most lately for the Oracle Dev Gym, an "active learning" website featuring quizzes, workouts a…

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…