PL/SQL Archives - Page 3 of 6 - Philipp Salvisberg's Blog

2019-12-13

2019-12-13

Categories

Oracle

Constants vs. Parameterless Functions

Do you use parameterless PL/SQL functions in your queries? Did you know that this may cause performance issues? In this blog post, I explain why parameterless functions can be the reason for bad execution plans in any Oracle Database. I recently had to analyze this problem in a production system and thought […]

2019-07-06

Categories

Oracle

Running utPLSQL Tests in SQL Developer

Introduction In November 2017 Jacek Gebal asked me if I could help to integrate utPLSQL into SQL Developer. In January 2018 we released the first MVP. Tests were executed in a new SQL Developer worksheet showing the result in the script output pane. This was easy to implement and it simplified the […]

2019-04-08

Categories

Oracle

Using DBMS_DEBUG in SQL Developer

Do you need to debug PL/SQL units in SQL Developer? You can’t get it to work because someone refuses to open TCP ports between your database and your client? No problem. You can still configure the good old DBMS_DEBUG in your SQL Developer. I know it is deprecated since Oracle Database 12c. […]

2019-02-17

Categories

Oracle

MemOptimized RowStore in Oracle Database 19c

Since February 13 2019 Oracle Database 19c has been available. I blogged about this feature here and here. Time for an update. So, what’s new in 19c regarding the MemOptimized Rowstore? Fast Lookup Works with JDBC Thin Driver I listed 16 prerequisites for the MemOptimized Rowstore in this blog post. The last one – […]

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

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