view counter

PL/SQL Feed

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

Speaking at UTOUG 2014

Oracle Database 12c PL./SQL ProgrammingI’m off shortly to attend and speak at the Utah Oracle User Group’s Spring Training Days 2014, which is held at Salt Lake Community College – Larry H. Miller Campus.

Results of 2013 Q4 PL/SQL Championship

You will find below the rankings for the 2013 Q4 PL/SQL championship; the number next to the player's name is the number of times that player has participated in a championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Peter Auer of Germany

2nd Place: Mike Pargeter of United Kingdom

Instant Column Addition

alter table from_11g add
column_with_a varchar2(100) default
'is instant provided you set it' not null;

Prior to 11g the above statement could take a very long time if the table was “big”. I was reminded of this by a recent discussion on Oracle-L.

Word 2010 – Add Template

You can add a Word Template without a problem until you get to Word 2010 because they’re hidden under the Developer ribbon, which is disabled by default. It’s funny something so widely used would be placed on a ribbon that is disabled by default.

It didn’t come up in the first few Google search pages. I figured that I’d blog it because it took me more than a couple minutes.

You can enable the ribbon with the following steps:

Oracle 12c Technical Hands-On Workshop

I have been running many tech events and briefings on Oracle 12c for India Partners. This time around, I thought of posting my latest event reviews. This week, I wrapped up the Oracle 12c Technical Hands-On workshop in Oracle facility, Gurgaon. It was a 2-day event from Oracle Database Product Management.

Find Type Dependents

Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:

The alternative to connect by for magicking rows from nothing

with recursive_query_subfactoring (
you_can_generate_rows, yes
) as (
select 1 you_can_generate_rows,
'from nothing!' yes
from dual
union all
select
you_can_generate_rows + 1,
rpad(yes,
13 + you_can_generate_rows,
'!') yes
from recursive_query_subfactoring
where

Inclusion Dependencies in SQL

Constraints have always been under appreciated in database practice. Today we’ll focus on inclusion dependencies, which in SQL community are better known as foreign keys. Let’s start with an example borrowed from David Spivak’s work on categorical foundation of databases — the familiar Employees table

Employees = [empno mgr dept]
               1    3   10
               2    2   20
               3    4   10
               4    4   10
               5    4   10

together with constraint

Bookreview: Oracle APEX Cookbook - 2nd edition by Marcel van der Plas and Michel van Zoest

A few weeks ago Packt Publishing asked me if I would give a book review for the recently released Oracle APEX Cookbook, the second edition by Marcel van der Plas and Michel van Zoest. In exchange I would get the e-book for free, so this can be considered a sponsored blogpost. Even though it is sponsored I will give my honest opinion about it.

SQL Quiz: The optimizer got it wrong. How can we make it do better?

The optimizer does particularly badly with the query below. It estimates the query will return 5,101 rows when in fact nothing matches the predicate:

view counter