Philipp Salvisberg’s Blog

Database-centric development

PinkDB PL/Scope Model Multi-temporal Features

Loading Historical Data Into Flashback Archive Enabled Tables

Oracle provides via OTN an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period/precision or ORA-1466 when using the AS OF TIMESTAMP […]

Joining Temporal Intervals Part 2

The solution I’ve provided in Joining Temporal Intervals produces wrong results if one or more temporal tables have gaps in their history or if disconnected intervals have the same content. In this post I’ll address both problems. Test Data The example queries are based on the same model as described in Joining Temporal […]

Merging Temporal Intervals with Gaps

In Joining Temporal Intervals I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post I’ll explain how to merge temporal intervals with various […]

Joining Temporal Intervals

From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a […]

Building Comma Separated Values with Oracle & SQL

From time to time I’m asked to aggregate strings from multiple records into a single column using SQL. Here’s an example, showing a comma separated list of ordered employee names per department based on the famous EMP and DEPT tables. Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If […]

Apple iPad Camera Connection Kit – What’s faster USB or SD Card?

I’ve just bought a Sony DSC RX100 camera with an Apple iPad Camera Connection Kit. The kit contains an USB adapter and a SD Card adapter. I intend to copy photos to my iPad for review and backup purposes during my holidays. My son argued that the SD card adapter must be […]

Using UTL_XML.PARSEQUERY for SQL Dependency Analysis

Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL/SQL Code Checking and Dependency Analysis. The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or […]

Where is the Symbian OS stuff?

I’ve released the last version of StartUp in December 1999 and the last version of Crypto in March 2002. That’s quite a long time ago. I don’t plan to work with Symbian OS anymore. If you need one of these programs (source or ARM or WINS  binary) just drop me an email […]