Posts tagged inline view
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 >
In a recent article – Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions – I discussed how to use Analytical Functions in SQL to cleverly (!) derive the valid periods from a database table that contains periods of inclusion and exclusion. A valid period is a period for which there is at least one inclusion and for which there is no exclusion. I used several powerpoint based graphics to illustrate the business case. For example:
to depict the periods of inclusion and exclusion and this figure to demonstrate how to derive the valid periods (the blue bars):
After completing this article – and fiddling around in Powerpoint quite a bit – I realized that for visualizing data in a table, I have a perfect tool at my fingertips: the Data Visualization Tags (DVT) in ADF 11g are created for this very purpose: turning data into information through visualization. And this rich library of DVTs components contains – in addition to fairly straightforward visualizations such as bar charts, pie charts and line graphs – also more complex visualization components such as the Bubble Chart, Thematic Map and Gantt Chart. The Gantt Chart has three More >
I have just completed my first ever presentation on the Expertezed.com network – http://www.expertezed.com/ , a reprise from my session on Oracle OpenWorld 2012. This presentation includes a number of slides regarding 12c features, based on the session and slides from Tom Kyte (Top 12 new features) and my notes from the excellent session CON8511 – Temporal Database Capabilities with the Latest Generation of Database Technology that I attended during the conference.
You can download the slides from this presentation here:Expertezed_OOW2012_TheVeryVeryLatestInDatabaseDevelopment.pptx .
Database development in the Oracle Database is crucial for creating well balanced multi tier applications. This presentation describes a number of useful facilities and application architecture considerations around the database, taking into account some of the most recent insights.
The official slide deck from this presentation at Oracle Open World 2012:
Update day to second interval column in selection from time gaps table by parsing strings containing time differences using REGEXP_SUBSTR2
An apparently trivial challenge that still took me bit longer than expected. What is the situation: I have a table with two columns (of interest). One is a column called gap, with values like these:
Each value represents a time difference. These values are strings – not much use to calculate with. Therefore a second column in the table – called timegap – is defined, of type INTERVAL DAY TO SECOND. The challenge I am facing is to set the value of this column timegap based on the time difference described by the string in column gap.
With a little bit of SQL agility, using for example some regular expressions and an interesting update on [the result of] an inline view, it turns out to be doable in a fairly simple SQL statement.