A few days ago I received a comment/question on the older post about dynamically transposing rows to column with Polymorphic Table Functions (PTF). Back then I overlooked a bug in the example code, but the explanation takes a bit longer, so i decided to write a new post about it. The PTF was working initially but after inserting new data started to return wrong results – all NULL’s for some columns where we know there are actually values present. So, what’s going on?
Continue readingCategory Archives: PTF
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 perception, it seems to be one of the most desired features in Oracle SQL! But let’s have a closer look. Is this really feasible and mature enough to be used in production code?
Continue readingPolymorphic 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 it is not enough to look at the currently processed row to produce the result. With table semantic PTF we often also need some summarized state from the previously processed rows. They are useful to implement user defined aggregate or window functions. Let’s first try to implement a very basic example of table semantic PTF and learn more theory as we go. Continue reading
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 will be column transposing. We’ll still define, which columns have to stay unchanged (as we already did using the parameter cols2stay). All other columns should be displayed as key-value pairs. Continue reading
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 of the removed columns as CSV in a new column? Continue reading
Polymorphic Table Functions (PTF) – Tinkering with Rowsets
Writing the second “basics” post on PTF I discovered, that there were much more details worth mentioning, than it would be acceptable for a “basics” post and would blow it up anyway 😉 So I decided to to separate the tests and finding in this (more deep-dive) post. Continue reading
Polymorphic Table Functions (PTF) , Part 1 – Basics
I have already posted some examples on Polymorphic Table Functions in Oracle 18c in the last months. I quickly realized how difficult it is to explain completely new feature using advanced examples and wanted to write a series of posts starting from very basics. Now that the Germany’s Oracle User Group (DOAG) has accepted my presentation on PTF for their annual conference is the time to do it.
Continue readingPolymorphic Table Functions Example – (NOT) Transposing Rows to Columns
It was not possible for me to write a follow-up to my last post about Transposing Columns To Rows with PTF showing an opposite task of transposing rows to columns right next weekend as I thought. Partly because of our awesome Trivadis TechEvent which took place back then and partly because this kind of the exercise turned out to be much more difficult one as supposed. Actually it is a nice example to see the limitations of the new feature. Continue reading
Polymorphic Table Functions Example – Transposing Columns To Rows
Hey, Oracle 18c is now available on the cloud and for engineered systems! For more than a week now. That also means you can play with it at LiveSQL. And of course you can try polymorphic table functions (PTF)! At least I’ve done that this weekend 😉 And here is my first working example. Continue reading
Polymorphic Table Functions
Last month I attended the DOAG conference in Nuremberg. As always, it was a great event, awesome community and excellent tech talks. And it seems that I’ve found what could be my favorite feature in the upcoming database release 18c. Keith Laker (@ASQLBarista), Oracle’s Product Manager for Analytic SQL, talked about “Building Agile Self-Describing SQL Functions For Big Data”. This title was promising enough for me and of course I wasn’t disappointed. Thanks a lot for an interesting presentation!
This blog post will be somewhat unusual, because I have actually no knowledge to share yet, but only the euphoria about the power and flexibility of the new feature. So what is it about?
Continue reading