Gear up for #AIOUG OTN Yathra’ 2016

Guys, AIOUG is back again with OTN Yathra’ 2016. It is a series of technology evangelist events organized by All India Oracle Users Group in six cities touring across the length and breadth of the country. It was my extreme pleasure to be the part of it in 2015 and I’m pleased to announce that … Continue reading

The First and Last Word on NoSQL

Amazon had the first word on NoSQL but the creator of relational theory, Dr. E. F. Codd, gets the last word.(read more)

Auto DDL: delete obsolete columns from table

A quick one. In the past I used to generate ddl based on queries, like the following. But I find myself to re-invent them again. So to have it saved for my offspring: here's one on deleting obsolete columns as generated on importing an excel sheet in SQLDeveloper:


declare
l_schema_name varchar2(30) := 'MY_SCHEMA';
l_table_name varchar2(30) := 'A_TABLE';
cursor c_cols is
select column_name
from all_tab_columns col
where col.table_name = l_table_name
and col.owner = l_schema_name
(more...)

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations
SELECT 'SCOTT' login_id 
,'X' alpha, 'Y' beta
,10 catgy1
,20 catgy2
,30 catgy3
,40 catgy4
,50 catgy5
,60 (more...)

WINDOW NOSORT STOPKEY + RANK()

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1
create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec  (more...)

Implementing Arc Relationships with Virtual Columns

Virtual columns were added in Oracle 11g Release 1. We can implement the same concept using views, but one of the advantages that I see in virtual columns is that we can define foreign key constraints on them. Well, we can define foreign key constraints on views as well, but only in DISABLE NOVALIDATE mode, which makes them a decoration rather than a data integrity protector. So to be more precise, we can define enabled (more...)

Renaming #EM12c / #EM13c Targets

Oracle Enterprise Manager is a complex piece of software that many organizations are running now. Some organizations set out with a formalized naming standard; some do not. Those who do not ofter end up identifying a naming standard later down the road and then making requests to change the names of the targets being monitored. In order to do this, there are two ways:

1. Delete and rediscover the target and rename at time of (more...)

SQL is Huuuuuuuge (Making SQL Great Again) Part III

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

SQL is Huuuuuuuge (Making SQL Great Again) Part II

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part VI

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part IV

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part V

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part VII

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

An old dog learns a new trick

Reports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff.

Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took (more...)

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations (more...)

Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

As I write this, I am listening to Kim Berg Hansen explain the MATCH_RECOGNIZE clause. He was kind enough to give me credit for some of the examples and mention this blog. In addition to my blog posts on the subject, you may enjoy my presentation on SlideShare. Please download it to see the animations!

Zerlegung von Tabellen in rowid ranges

Stew Ashton ist derzeit für einen signifikanten Anteil der ungelesen in meinem Blog-Reader wartenden Artikel verantwortlich, da er gerade eine vielteilige Serie zum Thema "Chunking tables" veröffentlicht hat (veranlasst durch eine entsprechende OTN-Anfrage von Jonathan Lewis bzw. White paper Bezugnahme von Bryn Llewellyn). Die genaue Anzahl der Artikel lasse ich aus, um diese Einleitung generisch zu halten und spätere Korrekturen der Anzahl vermeiden zu können. Freundlicherweise pflegt der Autor im ersten Artikel eine Liste der (more...)

SQL: Texte mit Umlauten und Sonderzeichen normieren

Im heutigen Beispiel möchte ich einmal kurz aufzeigen, wie Sie einen Text nach Ihren Bedürfnissen hin normieren können. Ich habe dazu die bekanntesten Länder der Erde in einer WITH Clause zusammengefasst und bilde mir anhand des Namens einen später verwendbaren normierten String.

Der Zielstring muss GROSSGESCHRIEBEN sein und darf keine Umlaute (ÖÜÄöüäß) und Sonderzeichen (, '.()-) beinhalten.
Mehr »

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
SELECT *
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
tab1col1 
              FROM   table2)


This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid (more...)