view counter

PL/SQL Feed

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

Finding All the Red M&Ms: A Story of Indexes and Full-Table Scans

In the database design quiz on the PL/SQL Challenge, a player made a comment referring to a 5% rule for indexes – i.e. that an index will (only) be used when accessing 5% or less of the rows in a table. This is something I’ve heard others refer to as well, so I thought I’d put together a post explaining how indexes work via the medium of chocolate!

SQL Brainteasers: Guess the Disease

I’ve not been too well recently, so to “celebrate” here’s six SQL clues to various diseases. Can you get them all? Put your answers in the comments!

Participants in the Q4 2013 PL/SQL Championship

The following players will be invited to participate in the Q4 2013 championship. The number in parentheses after their names are
the number of playoffs in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

We
currently plan to hold the championship on 27 February (all at the same time world-wide), and are waiting for confirmation from players that this day will work.

Participants in First-Ever Annual SQL Championship

The following players will be invited to participate in the first-ever SQL 2013 championship.

As you can see, we have "tweaked" the rules for championships as follows: the top 50 ranked players are now eligible to compete!

See the FAQ for an explanation of the three ways a player can qualify for the championship.

We currently plan to hold this championship on 12 February. 

And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

Participants in First-Ever Annual Logic Championship

The following players will be invited to participate in the first-ever PL/SQL Challenge Logic 2013 championship.

As you can see, we have "tweaked" the rules for championships as follows: the top 50 ranked players are now eligible to compete!

We currently plan to hold the championship on 19 February.

See the FAQ for an explanation of the three ways a player can qualify for the championship.

And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

The Database Design Quiz in Numbers: Player Feedback Stats

I’ve done more analysis of the first quarter of the Database Design quiz on the PL/SQL Challenge. This time I’ve had a look at the quiz survey results to see what did and didn’t like, whether or not the quizzes are too hard and what people want to see more or less of.
Here’s the figures:

Open a port on Fedora

Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. Naturally, it means you need to configure the /etc/ip6tables file to enable port 3306 after installing MySQL on Fedora.

You can open a port by adding the following line to the /etc/ip6tables file:

Free Shipping! Free Shipping! Free Shipping!

If you are one of the many humans who don't believe in evolution, or more specifically believe that the planet is just 6,000 years old, or believe that a God or gods have a plan for us, I wouldn't bother reading any more of this post.

PL/SQL Quiz: Why does the output of this procedure change (without changing it)?

Here’s another quiz. The procedure below is executed, “something” is run and the output changes on the next execution:
exec why_does_the_output_change;

from this

-- What happens here?

exec why_does_the_output_change;

to this

To cover off the easy options – no change is made to the code of the procedure and there’s no SQL within it (so the “mystery code” isn’t updating/inserting/deleting table data for example).
What did I do? Put your answers in the comments!

Cleaning up a Schema

My students wanted a simple way to cleanup a development schema. So I wrote the following anonymous block PL/SQL program, which also manages the fact that Oracle Database 12c doesn’t completely drop system-generated sequences in active sessions.

The new identity columns in Oracle Database 12c create system-generated sequences, which you must purge from the recycle bin. If you don’t a generic script, like the following raises:

view counter