Posts tagged sql
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 >
Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR1
Using Oracle SQL to resolve meaningful and slightly less serious challenges is one of my favorite (semi-)professional pastimes. In the last two weeks, I have been presenting on various topics including Oracle SQL to audiences in six cities all across India as part of the OTN Yathra 2013. These presentations and the interaction with the attendees on the various capabilities of SQL have inspired me in several ways. One of the outcomes is this article – also inspired by the fairly long journey home and the many flights within India. In this article I will use several powerful options in Oracle SQL to resolve some simple to ask questions. The SQL functions I am using include:
- Insert with Multiple Subqueries
- Insert generating some random data
- LISTAGG for aggregating strings
- LEAD to produce the result for one row using information from the next
- PIVOT to present the data in a matrix format
The statements are straightforward (relatively), the data model is simple. You will like it.
Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring0
The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:
The challenges I will discuss in this article are simple:
- which sequences of three color observations occur in the data set
- how often do these sequences of three occur (give me the top 3 sequences)
- given the colors that have been observed, which combinations of three can be created
- which possible combinations (or color sequences of three colors) have not been observed at all
Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.