view counter


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

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:

A little trick with redefining any object in a view from another schema

A couple months ago i used this trick with one of the dba_ views in own query for convenience.
Simple example:

SETI is a Grotesquerie

SETI - the Search for Extra-Terrestrial Intelligence

I used to be big into sci-fi. I read lots of fascinating stories involving ingeniously crafted aliens. It was very entertaining and thought-provoking.

Like many humans, I looked on with awe as humans found their way into space, peered ever deeper into space (and the universe as it existed long ago), and searched avidly for life on other planets.

Scrabble Word Finder in SQL

In addition to SQL I love games of all kinds. Scrabble is one of my favourite games, so I thought I’d combine these passions and build a scrabble word finder using just SQL!
First up, we need to create a table holding all the letters and the points values for each (adjust the points values accordingly if not using the English edition):

10 Formulas for Database Management

Something a bit different this time. Here’s some formulas relating to databases I’ve learned over the years:

Fedora Install of MySQL

I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the sudoers list, you should check this older and recently updated blog post.

The Database Design Quiz in Numbers

The first quarter of the database design quiz on the PL/SQL challenge finished last week. Being a numbers geek, here’s some facts and figures from how it’s gone so far:

To Buy or Not to Buy: FIJI Natural Artisan Water

Here's why FIJI Water thinks we should buy "their" water:

In Fiji, rainfall filters through volcanic rock over hundreds of years, adding vital minerals that give FIJI Water its unique and refreshing taste. The water collects in an ancient artesian aquifer deep within the earth, where it is protected from external elements. It's the way nature intended water to be. Untouched.

Here's why I think we should NOT buy this water:

view counter