As the most know regular expressions are very useful - problems which otherwise would require some programming (PL/SQL in our case) can be solved with a brief, compact syntax. And the best is that regexp support is built in the database kernel - which means that regular expressions can also be used for declaring check constraints - here is an example:

The internet is full of regexp samples. Also Oracle's Application Express leverages regexp in its form validations. The developer can declare a regexp validation - and APEX offers some regexp examples out-of-the-box.

I once had a discussion with a collegue how to extract the house number out of a postal address. And this should also work for (german) house number suffixes (1a, 10b, etc). We worked out the following regexp - and this example also shows how to use REGEXP_REPLACE.

And since in most cases anyone out there has already worked out the regexp I'm currently working on I created a public APEX application on apex.oracle.com. I'd like to collect useful regular expressionsone's here and would be happy if the community manages to build up a litte regexp library ...

Some time ago I published a blog posting how to generate a SQL script which creates a BLOB from binary content. The contained function accepts a blob and returns a pure PL/SQL script which generates that blob. This might be useful in cases where BLOB data has to be inserted into tables and exp or imp are not available. The blob is created with "pure" PL/SQL.

I now published this function as an APEX app on apex.oracle.com. After uploading a file you get the PL/SQL script which generates that file as a BLOB in your database.

Short time ago I was asked a question - I had never
thought that someone really need this stuff, but then it got clear that every bus, train
oder flight schedules are printed that way: How can the result of a SQL query (one column) be
distributed over 10 (or say: X) columns?. Let's assume we have the following
query result ...

And of course we can do this with procedural logic (PL/SQL- or Table Functions). But for now we want to achieve this with just a SQL query. And this has advantages. A pure SQL query can directly be used by reporting tools or PDF generators. So its an interesting question: And since I'm sitting in a train for the next two hours now this is a good opportunity to write a blog posting about it.

An approach to the problem is to generate line and column numbers for our "matrix" report. This is an easy task - we have modulo and division operators in the SQL language. The first step (for five columns) then looks like this:

Due to the modulo operation the last (fifth) column has the smallest number (zero) but this is so far no issue ... the rest of the problem is now a typical pivoting operation. And we're lucky: in Oracle11g there is the SQL PIVOT clause:

So Oracle11g provides an easy solution for this problem. But what if we don't have Oracle11g. I had a blog posting about pivot tables or matrix reports in previous versions in the past. The PL/SQL function getPivotSql can help us here - it generates the final SQL query for us ...

... which is not exactly what we want - the resulting SQL query has to adjusted a little bit. The first thing is simple: Add an order by zeile at the end to have correct row order. The second issue is the column ordering. As seen above the columns are dermined by the modulo operation. And for the last column the modulo is zero (0). So the getPivotSql function places this column as the first one.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.