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:
A small enhancement, but what a golden nugget! At least that’s what I think.
It is now possible in Oracle 12c to export data via a view. I my realm, Oracle XMLDB, that can be an interesting feature to export stored XML content in a relational format and import it, for example, in a relational table/environment. Let me show you how.
Lets create an XMLTYPE table and insert some XML into it: (more…)
The new Database Express or DB Express database utility is different from the former DB Console. It is based on flash and “built-in” the database. To give you a small overview (sneak peek) into this great new tool for, for example, database performance tuning, hereby some pictures to create some appetite. I created some have load on my database, to make it a bit more interesting…Main Menu
The main menu has a section for “Configuration”, “Storage”, “Security” and “Performance” (more…)
Oracle DB Express is the new replacement for Oracle DB Console in the Oracle 12c database. Here are some first steps to get started.
Oracle DB Express makes use of what is called the “XDB Protocol Server”, which among others supports the Oracle APEX standalone PL/SQL Gateway. For people who are unknown still how to set this up, hereby some ideas how to get started with this cool new administrative alternative.
First enable the HTTP functionality of the XDB Protocol Server. You are allowed to do this via the SYS account or have the XDBADMIN role.
- Check if it is enabled via:
Is this one of my highlights? No not really, but it is a remarkable one. Oracle XMLDB functionality is now mandatory and can not be uninstalled. As mentioned in the new feature section of the Oracle XMLDB Developers Guide for Oracle 12c:
Oracle XML DB is now a mandatory component of Oracle Database. You cannot uninstall it, and there is no option not to include it when you create Oracle Database. It is automatically installed when you create a new database or (if not existing already) when you upgrade an existing database to Oracle Database 12c Release 1 (18.104.22.168).
…and a bit further on… (more…)