SQL Archives - Page 5 of 7 - Philipp Salvisberg's Blog

2018-08-05

Categories

Oracle

View-API for JOOQ Application

In this blog post, I show how to build a read-only view-API for Oracle’s HR sample schema. And I will use this view-API in a JOOQ application. This application will fully comply with the Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible. […]

2018-07-18

Categories

Oracle

The Pink Database Paradigm (PinkDB)

1. Introduction The Pink Database paradigm (PinkDB) is an application architecture for database-centric applications. It focuses on relational database systems and is vendor-neutral. The principles are based on the ideas of SmartDB, with some adaptions that make PinkDB easier to apply in existing development environments. An important feature of a PinkDB application […]

2018-07-18

2018-07-18

Categories

Oracle

Is Your Application SmartDB?

I recently had a few discussions regarding the Smart Database Paradigm (SmartDB) with long-standing customers, new customers, partners, competitors and colleagues. Some people think that using APEX and PL/SQL in their database application is SmartDB. But it is not that simple. Bryn Llewelyn defined the term “Smart Database Paradigm” (SmartDB) in his talk Guarding […]

2018-06-19

Categories

Oracle

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

2018-06-10

Categories

Oracle

MemOptimized RowStore in Oracle Database 18c

The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve the 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 […]

2018-05-06

Categories

Oracle

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 having a look at the documentation or reading Steven Feuerstein’s blog post. In this blog post, I talk about how to use this feature properly. Consider you have a […]

2018-04-30

Categories

Oracle

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

2017-10-15

Categories

Oracle

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

2017-10-14

Categories

Oracle

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

2017-07-11

Categories

Oracle

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