Philipp Salvisberg’s Blog

Database-centric development

PinkDB PL/Scope Model Multi-temporal Features

MemOptimized RowStore in Oracle Database 18c with OCI

On June, 10 2018 I blogged about the MemOptimized RowStore in Oracle Database 18c. If you haven’t read this post, it is a good idea to catch up now. I showed that accessing a memoptimized table t4 via the MemOptimized RowStore was around 60% slower than accessing a heap-organized table t1. I […]

MemOptimized RowStore in Oracle Database 18c

The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve performance of simple queries accessing data via primary key columns only. An example of such a query is SELECT value FROM t WHERE key = :key where key is the only primary key column of table t. This feature is available […]

Why and How Using the accessible_by_clause

The accessible_by_clause was introduced in Oracle Database 12 Release 1 and extended in Release 2. If you don‘t know this feature, I suggest to have a look at the documentation or read Steven Feuerstein’s blog post. In this blog post I talk about how to use this feature properly. Consider you have […]

Accessible PL/SQL Programs in Oracle Database 18c

I’ve recently installed plscope-utils in an Oracle Database 18c instance. A package body using the SYS.UTL_XML.ParseQuery function failed to compile. The error message was: PLS-00306: wrong number or types of arguments in call to ‘PARSEQUERY’. Fixing that was easy. I just had to pass the new mandatory currUid parameter. But then I’ve got the […]

PL/SQL Cop for SonarQube 7.0

The last two months my Trivadis colleague Daniel Schutzbach and I have been working on the PL/SQL Cop plugin for SonarQube. The goal was to support the most recent SonarQube versions 5.6 LTS, 6.7 LTS and 7.0. Dani was doing the heavy lifting and my job was testing and minor bug fixing. […]

How to Prove That Your SmartDB App Is Secure

If you are guarding your data behind a hard shell PL/SQL API as Bryn Llewellyn, Toon Koppelaars and others recommend, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views […]

Entity Relationship Model for PL/Scope

Today I found a sketch of an ERD from last year when I looked at the new features of PL/Scope in version 12.2. It looked a bit complicated and also wrong. So, I decided to refactor it using SQL Developer Data Modeler and share the result. You find the model in the […]

Limitations of PL/Scope and How to Deal with Them

My first car was a Renault R5 TX. The motor cooling of this car was really bad. On a hot summer day it was simply not possible to drive slowly in high traffic without overheating the engine. To cool the engine you could either stop the car, open the front lid and […]

Continuous Code Quality for PL/SQL with Docker

In this blog post I show step by step how to set up a continuous code quality inspection environment for a PL/SQL project hosted on GitHub. I’m going to use a Docker container for SonarQube and another container for Jenkins. Here is the table of content of the major steps. Install Docker […]

Simplify the Use of PL/Scope in SQL Developer

In this post I showed how to do some code analysis with PL/Scope and how the views and packages of the plscope-utils might simplify this task. However, these views and packages are based on dba_* views and it is sometimes not that easy to get such privileges for an additional user in a non-personal database instance. […]