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!

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...)

Chunking tables 8: second solution

Here is the second complete solution to the problem of dividing a table into chunks of equal size. This is the solution whose first version was marked "correct" on OTN. It is the basis for what Bryn Llewellyn calls "Approx_Method_Sql_Ashton_2" in his white paper.

Chunking tables 7: prior sys_guid() ???

The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.

Chunking tables 6: JOIN solution

At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.

Show all views including a specific string

Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail:
Mehr »

Oracle Spatial (Teil 3) – Entfernung von Punkten berechnen

Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMTRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.
Mehr »

Importing XML file with invalid character 22 (U+0016)

I have to import a set of XML files from time to time. Most of those XML files can be imported with out any problems. But at least one file includes a special character U+0016 which occurs randomly some where inside the file.

When I try to import that file I get this ORA- error message:
ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung
LPX-00217: Ungültiges Zeichen 22 (U+0016)
Error at line 39409 aufgetreten

Mehr »

How an Oracle error can send you the wrong way…

Update multiple columns

Using Apache Drill REST API to Build ASCII Dashboard With Node

Read this article on my new blog Apache Drill has a hidden gem: an easy to use REST interface. This API can be used to Query, Profile and Configure Drill engine. In this blog post I will explain how to use Drill REST API to create ascii dashboards using Blessed Contrib. The ASCII Dashboard looks like Prerequisites Node.js Apache Drill 1.2 For this post, you will use the SFO

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on 11.2.0.4

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.

(more...)

Fundamentals of SQL Writeback in Dodeca

One of the features of Dodeca is read-write functionality to SQL databases.  We often get questions as to how to write data back to a relational database, so I thought I would post a quick blog entry for our customers to reference.

This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases.  The example will use a simple Dodeca (more...)

Add Color to your SQL

Topic: this post is about some simple techniques to add color to SQL scripts and their terminal output using ANSI escape codes.

Colors can be used to improve the output of command line tools. This is common practice, for example with the (bash) shell. Colors can also be very useful to improve the quality of the output of SQL scripts. In my experience this is not used frequently, probably because of the the need of (more...)

Autonomous transaction to the rescue