Devel. + PL/SQL tools
JDeveloper, Forms, Designer,…
One of the very nice new features in SQL in Oracle Database 12c is the MATCH_RECOGNIZE operator. MATCH_RECOGNIZE adds a facility for spotting patterns in records. This allows us to locate records that are part of some kind of pattern relative to other records. It does sound similar to what Analytic Functions – most notably LAG and LEAD can do – but it is different. LAG and LEAD allow you to calculate the result of a record based on other records in the result set – but you are very limited in the ways in which you can indicate which other records in the result set are to be referenced by LAG and LEAD. Typically, it will be a fixed number of records before or after the record itself.
MATCH_RECOGNIZE allows us to have the database find a pattern – a regular expression expressed in terms of row conditions – in a far more flexible, dynamic and almost fuzzy way.
In this article, I will use this functionality to find the most valuable player in a football team (US readers: I mean the sports that you may refer to as Soccer). My definition of the MVP is the player who is most frequently part of a period of uninterrupted ball possession ending with a goal. Whether the play scores the goal, More >
Flashback started out as a feature in Oracle Database 9i. Although to be honest it is just the opening up of a mechanism that has been at the core of the Oracle Database from very early on: the ability to have concurrent sessions and transactions and allow transaction rollback and long running queries unaffected by transactions completed after the start of the query are all based on the same mechanism as flashback.
Flashback was great for demos and it was great for administrators. Developers however could not to very much with it. At least not until Oracle Database 11g when the Flashback Data Archive was introduced that provided fine grained control over which tables should have flashback data associated with it and which would not. At the time, the FDA was part of the Advanced Compression Database Option on top of the Enterprise Edition. So:great feature but for the happy few. In addition, there still were two main limitations with Flashback: history starts only at the day when the FDA is created. Nothing from before that day would be available. It’s a bit like the butterfly that does not have any of the memories of the caterpillar. The second limitation: Flashback did not record More >
Oracle 8i (8.1.6 if I remember correctly) started with the introduction of a that wonderful new phenomenon in SQL: Analytical Functions. Functions that basically allow the result for one row in the result set to be calculated using the values in other rows. This allowed to look forward (lag) and back (lead) in result sets as well as calculate aggregates across partitions in every direction. Analytical Functions have helped to produce quite advanced analyses of data using very elegant, compact and surprisingly well performing queries.
With Oracle Database 12c, our SQL just got a little better equipped to perform analysis. The MATCH_RECOGNIZE operator has been introduced – one you may know if you have ever dabbled in CQL, the Continuous Query Language that is at the heart of several complex event processors. This operator goes beyond Analytical Functions in its capabilities to analyze a data set. The comparisons MATCH_RECOGNIZE allows us to make between rows in order to decide whether or not to produce a result are more advanced (and sometimes more fuzzy) than the straightforward comparison operators available with analytical functions. That sounds a little cryptic. Let’s look at More >
Joining is a key ingredient in most SQL queries. Using collections (aka nested tables) that are produced on the fly inside the SQL query or that are returned by a PL/SQL function that is invoked from a TABLE operator in the query is a powerful weapon as well. Oracle Database 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example:
Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack0
Oracle Database 12c ships with a brand new supplied package called UTL_CALL_STACK. This package provides an API for inspecting the PL/SQL Callstack. The package complements the DBMS_ UTILITY.FORMAT_CALL_STACK that returns a pretty print human readable overview of the callstack.
Here is an example of how this package could be used in your own PL/SQL code:
Dynamic depth is an indication for the number of items there are on the call stack underneath the current scope. The entire stack can be traversed, from 1 (the very first PL/SQL unit that started the whole thing) to the current one. For each level on the call stack, the name of the subprogram can be retrieved, as well as the line number from where the call was made to the next level in the call stack.
At long last it’s here: Oracle Database 12cR1. The download of this fresh software package is hardly done. It is time to start sharing some of our early experiences with this major milestone in the history of Oracle’s flagship product.
The importance of the 12c release lies not necessarily on application development and the further advancement of SQL. Nevertheless, that is where my heart lies and that is where quite interesting new features and functions have been added as well. This article is not about cloud, pluggable databases, information lifecycle management or other administration boons. It is on SQL. Good old – and now good new – SQL.
The first feature I want to introduce is the Inline PL/SQL function.
As an Oracle Database developer you should be aware of the In-Line View – introduced in Oracle 9i, quite some time ago. This In-Line View allows to create Views-on-the-fly inside the scope of a SQL query. The query no longer has to start with SELECT. Instead, it can start with WITH and the definition of one or more views that exist only inside the query and for the duration of the query but otherwise share many of the characteristics of real database views (at least in the More >