Misconceptions about (Regular) Views Revealed when Presenting Editioning Views

Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.

It happens to me almost every time I talk about Edition-Based Redefinition. I present Editioning Views, and then I get questions that reveal misunderstandings about views in general.

One such misunderstanding is regarding what is kept in the view definition.

When we create a view as “select * from table”, the * is expanded to actual (more...)

Using dynamic tooltips in your Interactive Report

Inside an Interactive Report (IR) I had a comment column. The comments in this column could become really large and the users wanted the comments to be automatically trimmed if more then 60 characters were displayed. If the user moved the mouse above a trimmed comment then a tooltip should be display including all comment text.

My first idea was to check for existing plugins which could do this job for me. So I searched (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

PREDICTION_DETAILS function in Oracle

When building predictive models the data scientist can spend a large amount of time examining the models produced and how they work and perform on their hold out sample data sets. They do this to understand is the model gives a good general representation of the data and can identify/predict many different scenarios. When the "best" model has been selected then this is typically deployed is some sort of reporting environment, where a list is (more...)

Accessing STATUS columns efficiently

A frequently reoccuring design problem with relational databases is the issue locating unprocessed rows in a large table, so we know which rows of data are still yet to be processed.

The problem with a STATUS column is that it generally has low cardinality; there are probably only a handful of distinct values [(C)omplete, (E)rror, (U)nprocessed or something like that]. Most records will be (C)omplete. This makes STATUS a poor candidate for standard B-Tree indexation. (more...)

Joining to a pipelined table function and “left correlation”


A pipelined table function may be called from regular SQL using the TABLE collection expression, e.g.

FROM   TABLE(my_pipelined_function('ABC','DEF'));

where ‘ABC’ and ‘DEF’ are the inputs to the function.

What if you want to call the function repeatedly for several sets of inputs, e.g. testing the function for a variety of values? If those inputs are stored in a table somewhere, it ought to be as easy (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

The best idea since 1992: Putting the C into ACID (We need your vote)

Oracle Rdb (only available for the VMS platform) supports SQL-92 assertions (http://community.hpe.com/hpeb/attachments/hpeb/itrc-149/22979/1/15667.doc) so why not Oracle Database? Let’s put the “C” into “ACID.”(read more)

Subtleties – Part 2 (Nested Tables and Varrays)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list

Subtleties – Part 1 (SQL and PL/SQL)

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table  (more...)


For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:


This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Who Rewrote My SQL?

There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates).
In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write Less (Code) with More (Oracle12c New Features)” presentation. I usually talk about another such feature in this presentation (more...)

Mustererkennung mit MATCH_RECOGNIZE in 12c

Keith Laker hat im Data Warehouse Insider Blog eine Artikelserie begonnen, die sich mit der Verwendung des MATCH_RECOGNIZE Features in 12c beschäftigt. Ich will jetzt nicht behaupten, dass ich die Artikel umfassend wiedergeben würde (ja nicht einmal, dass ich sie komplett gelesen hätte), aber sie wären sicherlich ein sehr nützlicher Einstieg, sollte ich gelegentlich auf die Idee kommen, das Feature irgendwo einzusetzen. In erster Näherung greife ich hier aber nur die Zusammanfassungs-Abschnitte am jeweiligen Artikelende (more...)

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

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


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:

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

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

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

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 »