view counter

PL/SQL Feed

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

Signal from a procedure

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

Results of First-ever SQL Championship for 2013

You will find below the rankings for the first ever 2013 SQL championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Vincent Malgrat of French Republic

2nd Place: Christoph Hillinger of Austria

Brush up your PL/SQL skills with Steven’s new book

insert into my_reading_list (
book, author
) values (
'Oracle PL/SQL Programming, 6th Edition',
'Steven Feuerstein'
);
That’s right, Steven Feuerstein – PL/SQL expert and all round great guy – has recently published the 6th edition of his PL/SQL programming book.

Union via Join

Here is really bizarre technique: one can perform union via join! Consider:

(([p] 0 [q] a b) ([p] 1 [q] c)) v [q];

which outputs

[q]
a
b
c

(We assumed domain p to include {0,1} and q to include {a,b,c}, at least). You may object that the expression involves [domain dependent] union operation , but it is applied for two relations with disjoint attributes, so it is nothing like classic union.

Oracle 12c: scalar subqueries

We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):

SYS_OP_MAP_NONNULL is in the documentation now

Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views
Lazy tip: By the way, with length limitations, we can also use documented dump function:

SQL Quiz: Why does this query return no data?

Below is a direct copy and paste of some code I ran (nothing is done in between the sections). There’s clearly data in the tables, but it vanishes when they are joined:

What programming languages do I use?

This month's Roundtable discussion on the PL/SQL Challenge is:

If you are on this website, you almost certainly know PL/SQL and SQL. What other programming languages do you currently use? How do you find they compare to PL/SQL and SQL?

Triggering and Handling a Custom Event in APEX

On one of the pages there was a need to refresh a report region when a node in an APEX Tree was clicked. As far as I know there is no built-in method to create a Dynamic Action to a node in an APEX Tree, so this might call for a Custom Event.
If there is a built-in method, please leave a comment :)

A Letter from "Oyster Shel"

My father, Sheldon Feuerstein, was a large presence in our lives and the lives of many others. He was a big man, physically (and, sadly, overweight for too many of his later years, which contributed to the diseases that led to his death in 2010), but it was more than that. He was smart and honest and eloquent. He could also be very stern and, before he passed middle age, manifested quite the temper. But he was a deeply compassionate man, especially for members of the family. Many nieces, nephews and cousins remember him with a love and deep fondness that sometimes surprises me.

view counter