view counter

PL/SQL Feed

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

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

12c MATCH_RECOGNIZE: Grouping sequences

The Tabibitosan method by Aketi Jyuuzou is a very clever and efficient way to group rows with consecutive values. When it solves the problem, it can’t be beat — unless you have 12c. This method is worth explaining in its own right, so I’ll do my best; then I’ll make it easier with the MATCH_RECOGNIZE […]

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.

Database 12c MATCH_RECOGNIZE: Read all about it

I plan to update this post as new information appears on the Web. 2014/03/06 Galo Balda has published his presentation “SQL Pattern Matching in Oracle 12c”, made at RMOUG on February 6th. He shows three different use cases so it’s pretty concrete. One thing I saw for the first time was using the FINAL keyword […]

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:

PostgreSQL Install-Windows

A number of folks have asked me to create a post of a PostgreSQL installation on Windows. So, here is my step-by-step PostgreSQL 9.3 installation.

Download PostgreSQL Database

You can start the download of PostgreSQL from this site. It leads you to the EnterpriseDB site, where you can download the prepackaged software.

Single SQL vs SQL+PL/SQL

Everyone knows Tom Kyte’s mantra:
You should do it in a single SQL statement if at all possible.
But we all know that “Every rule has an exception
There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:

Add User Defined Types

Somebody asked me if there was a cheaper alternative to using the Embarcadero data modeling tools. I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Surrogate keys don’t fix normalization problems!

alter /* unnormalized */ table
is_still_unnormalized_after add (
surrogate_key_id integer primary key,
to_it varchar2(1)
);

Normalization is to do with the dependencies between the (business) attributes. As surrogate keys are additional system generated identifiers, they do nothing to change the dependencies between the original attributes!

view counter