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…
How to Save 7 Keystrokes with dbms_xplan
An almost unknown simplification in the usage of dbms_xplan is possible since Oracle 12.2. Sometimes, there are small details that makes our job (a little bit) easier. In one of his presentations at the Kscope19 conference, Kim Berg Hansen mentioned an interesting detail about the syntax…
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…
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…
MemOptimized RowStore in Oracle Database 19c
Since February, 13 2019 Oracle Database 19c is 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…
ORA-22905 when calling ODCI Table functions from PL/SQL
Back in 2015 I experimented using ODCITable* functions to dynamically parse delimited text (see blog post here.) Now blog reader Daniel Cabanillas points out, that it raises an error when used in PL/SQL. Silly me – I had only tried it out in SQL (my…
Dynamic Pivot with Polymorphic Table Function?
LiveSQL is great place to start playing with new features. It provides a couple of very helpful demo scripts explaining how polymorphic table functions work. There I found a new script few days ago which uses PTF for dynamic pivot! WOW! According to my subjective…
Polymorphic Table Functions (PTF), Part 4 – Table Semantic PTF
In the first three parts of the series we have seen how a PTF basically works, but we have focused on row-semantic PTF only. In this part we’ll look at the table semantic PTF. As a reminder, the main difference to row-semantic PTF is that…
Polymorphic Table Functions (PTF) , Part 3 – Row Replication
In the third part of the PTF-series we learn how a PTF can change the cardinality of the input data flow: return more or less rows as in the input. We’ll use the same simple table as in the part 2 and our new task…
Polymorphic Table Functions (PTF) , Part 2 – More Basics With Some Deep-Dive
In the first part of PTF series we went through a very basic example removing some columns and adding a new column with a constant value. Starting from the same example we’ll do something more meaningful in the second part. How about concatenating the values…
Social Media @trivadis