view counter


Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

23 December Quiz Now Available

For those of you checking for news on the PL/SQL Challenge site via the blog (and not the plsqlchallenge twitter account, which we encourage you to follow):

The quiz for 23 December is now being displayed properly.

Our apologies for the invalid image error and display.

All answers submitted prior to 13:45 were removed, so you can take the quiz again without simply guessing.

If we end up with 475+ answers for the day, I probably will not void the results.

Improving Website Performance: An Update

I announced earlier this week that I was suspending all work on new features until we improved the performance on the site.

Since then we have done the following:

Christmas Break

update sqlfail
set next_post_date = date '2014-01-06';
I’m taking a bit of a break over Christmas while I spend time with my daughter and her grandparents. Thanks to everyone who follows this site, hope you have a great Christmas/festive time and 2014 is everything you hope for. :)
In the meantime, there’s a message hidden in the SQL below for you (note, you’ll need to run this on to guarantee it works):

OUT Parameter Trick

Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an OUT-only mode parameter from a procedure.

WITH Clause Functions

A neat feature of Oracle Database 12c is the ability to put PL/SQL functions inside SQL WITH statements. It’s covered in Chapter 2 on new SQL and PL/SQL features of the Oracle Database 12c PL/SQL Programming. There’s a trick though, you must disable the SQLTERMINATOR before creating the statement or accessing it, like:

PL/SQL Challenge Website Now Available

Just in time for Tuesday!


Well, you know how you just sometimes have one of those days?

This was one of mine.

I am still analyzing the full extent of my stupidity and incompetence (and I fear the results may be classified).

But I can say this:

First of all, thank you, John Scott, for being available and so, well, non-judgmental, as you once again bailed me out.

Poor, Sad Website: Voiding Today's Quiz

At 9:45 AM Chicago time, the PL/SQL Challenge website became unresponsive. It looks like we were and are being hammered by bots. We have been unable to stop the onslaught and so after a few hours of on again, off again access, I must announce with great sadness that we will void the results for today's quiz.

This means they will not be included in rankings for the quarter.

Now, having said that, I plan - when the site and backend become available again - to see how easily we can extend the end date for today's tomorrow (make it a "two day quiz").

Temporal validity(history) in Oracle 12c

In Oracle 11g You can store the previous states of the a table in a Flashback Archive.
Now, starting with Oracle 12c, You can store the old and current states of the rows in the same table.
This is extremely important for dimensonal tables in a Datawarehouse,
because we may want to keep the whole history of a dimension table.
(Consider SCD2 Dimensions)
We can explicitly or implicitly define date/timestamp type columns
that are responsible to store the “lifetime” period of a particular row.

SQL Brainteasers: Guess the Christmas Carol/Song

Christmas is approaching, so some Christmassy-themed songs to guess. Seven to get this time (because it’s Chrismtas ;). As always, put your answers in the comments!

PL/SQL Quiz: How can we stop this function raising NO_DATA_FOUND (without changing it)?

Another puzzle, when we execute the function below it returns NO_DATA_FOUND because the table is empty:

view counter