Philipp Salvisberg’s Blog

Database-centric development

PinkDB PL/Scope Model Multi-temporal Features

Trivadis PL/SQL & SQL CodeChecker for SQL Developer Released

A half a year ago Trivadis released a command line utility to scan code within a directory tree for guideline violations of the Trivadis PL/SQL & SQL Coding Guidelines Version 2.0. This is tool is perfectly suited to process millions of lines of code, but an integration into Oracle SQL Developer was missing until […]

Multi-temporal Features in Oracle 12c

Oracle 12c has a feature called Temporal Validity. With Temporal Validity you can add one or more valid time dimensions to a table using existing columns, or using columns automatically created by the database. This means that Oracle offers combined with Flashback Data Archive native bi-temporal and even multi-temporal historization features. This […]

Column-less Table Access

While writing some JUnit tests after fixing bugs in dependency analysis views, I came up with the following query: [crayon-5f8e787917afb846201036/] The first view tvd_object_usage_v contains all table/view usages per object. The second view tvd_object_col_usages_v contains all column usages per object. The idea was to check the completeness of the second view tvd_object_col_usages_v. […]

Trivadis PL/SQL & SQL CodeChecker Released

In August 2009 Trivadis – the company I work for – released the first version of their PL/SQL & SQL Coding Guidelines. Back then we made our PL/SQL assessments based on interviews and checked the code against our guidelines using Code Xpert, SQL*Plus scripts and some manual/visual checks. You may imagine that […]

Trivadis PL/SQL & SQL CodeAnalyzer Released

A month ago I had a talk about “Extending the Oracle Data Dictionary for Fine-Grained PL/SQL and SQL Analysis” during the ODTUG Kscope13 conference in New Orleans. Oracle data dictionary views as DBA_IDENTIFIERS or DBA_DEPENDENCIES are in many cases sufficient to analyze static PL/SQL and SQL code within the Oracle database. But what […]

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 […]