Oracle Virtualization and Cloud Consulting
view counter


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

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:

12c MATCH_RECOGNIZE and the “start of group” method

Please don’t think this post is “just” about row pattern matching and MATCH_RECOGNIZE. I am going to present the “start of group” method as demonstrated by Timur Akhmadeev and Solomon Yakobson. This is a powerful use of analytics to group data based on comparisons between adjacent rows. It’s a technique worth knowing while waiting for […]

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
you_can_generate_rows + 1,
13 + you_can_generate_rows,
'!') yes
from recursive_query_subfactoring

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 […]

view counter