Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)
Because today is Batman Day
, it calls for a celebration of the SQL type:
SQL> select listagg (b)
2 within group (order by null) ||' Batman'
3 from (select 0f/0 b
4 from dual
5 connect by level <= 14
Oracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In this set, each record satisifies a certain condition. The fact that in a set of records these conditions are met – in a certain (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT. This blog article by Natalka Roshak (2015) compares various row generation (more...)
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
FROM dept d
SEMI JOIN emp e ON (e.deptno = d.deptno)
to get all the (more...)
firstname.lastname@example.org > create table t( pk int primary key check(pk > 0));
email@example.com > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
firstname.lastname@example.org > select trunc(dbms_random.value(1, 1e5)) from dual
email@example.com > connect by level <= 1e5
firstname.lastname@example.org > /
63187 rows created.
Finding the first n gaps
email@example.com > variable n number
firstname.lastname@example.org > exec :n := 1000
PL/SQL procedure successfully completed.
email@example.com > set autotr (more...)
I had a scenario today where I was loading a table and a particular column is known by multiple names in different source systems and thus to different people. In order to make everyone happy on this occasion, I wondered if I could create a normal column for one of the multiple names and then use virtual columns pointing at the normal column, for the other names.
I’m aware there are several ways of skinning (more...)
Could you tell me what the difference is between NVL and COALESCE?
NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)
Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story..
For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, whether there is any discrepancy between the schema that was used to build the application (.ear) files and the target schema of the deployment. Of (more...)
I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?
Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)
Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
Proud “parent” of a bouncing baby book
Today a box arrived from Oracle Press. In it were a few copies of “Real-World SQL and PL/SQL” which I co-authored with Arup Nanda, Brendan Tierney, Heli Helskyaho and Alex Nuitjen. I know I only blogged about the book a couple of weeks back, how I became involved and the impact it had on my life for several months, but as I can now physically handle and read (more...)
Apache Drill is an engine that can connect to many different data sources, and provide a SQL interface to them. It's not just a wanna-be SQL interface that trips over at anything complex - it's a hugely functional one including support for many built in functions as well as windowing functions. Whilst it can connect to standard data sources that you'd be able to query with SQL anyway, like Oracle or MySQL, it can also (more...)
I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.
The British part of me wants (more...)
and Todd Hershiser gave me some very valuable feedback through their comments on my recent "Splitting Strings" posts. The big news is: PL/SQL beats SQL!
In my previous post
to split a comma delimited string. Now I'll apply that technique to multiple rows, and show that it's faster than other methods.
hat vor kurzem einen interessanten Artikel zu einem Thema veröffentlicht, mit dem man sich beim Schreiben komplexerer SQL-Queries regelmäßig herumschlagen muss: dem Vergleichen von Spalten, in denen NULL-Werte auftauchen können. Für die Prüfung der Gleichheit von Werten bedarf die korrekte Behandlung von NULL-Werten bereits eines recht sperrigen Ausdrucks:
column1 = column2 or (column1 is null and column2 is null)
Und noch unhandlicher wird der Ausdruck, wenn man die Ungleichheit von Werten prüfen möchte: