view counter


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

Personal Cloud Storage - Oh Yeah!

I hate advertising, but I sure do find the machinations and twisty-thinking of marketeers to be quite entertaining. Here's an especially delightful example:

SQL Quiz: How can we fix this query (without changing it)?

A quiz of a slightly different kind this time. The following SQL raises an ORA-01722 (invalid number) exception:
select *
this_simple_join tsj,
does_not_work dnw
where tsj.column_1 = dnw.column_1
and tsj.column_2 = dnw.column_2;

ORA-01722: invalid number

We can fix this so no error is returned without changing the SQL statement itself. How is this possible?
Put your answers in the comments! I’ll post my full solution on Monday.

Justify, a poem


by Steven Feuerstein, 2013

I should not be here.
I should not be.

Too many humans
devouring the world.

I would leave
to save a single tree
or to ensure
a fern leaf unfurled.

But I'll stay, instead,
and switch allegiance
from a genocidal species
to my planet, my home:
my home of surpassing beauty.

If I stay, instead,
and save a single tree
and help tree ferns
unfurl their leaves,
perhaps I could at least
this poem.

APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve that.
It is also possible to create a Pipelined Table Function which can be used a the basis of the APEX Tree. In this blogpost I will show you how to do that.
The first that we will need are some Type definitions, starting with the Object Type:

Loey Goes to Youtube!

I finally got around to posting some videos of Loey on Youtube. Enjoy!

Loey in a Puddle, Loey the Big Lady

Spent the latter half of Sunday with Lovely Loey.

Stormy day (lots of tornados south of Chicago) and both Veva and Loey enjoyed stomping in some puddles in their big yellow boots!


The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:

ID V1 V2 V3 V4 V5
---------- ----- ----- ----- ----- -----
1 a b e
2 aaa bbbb cccc
3 a b e
4 a c e
5 b d

How Do You Drop a Materialized View Without Losing the Computed Data?

Something I spotted on the Oracle-L mailing list the other day:
drop materialized view
and_keep_the_results_you preserve table;
For a script, keep reading.

Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.
In this blogpost I will assume that you created the APEX tree as described in the other two blogposts (the links to these blogpost are at the bottom).

SQL Brainteasers: Guess the Book Title

A bit more highbrow this time, can you guess the book titles from the SQL clues below?

view counter