Wednesday, 30 November 2016

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 to allow you to add an extra layer of security to your PL/SQL objects.

Tuesday, 29 November 2016

Oracle Multitenant : Super-fast Provisioning of Standby Databases

I will demonstrate how to quickly create a standby database for a non-multitenant container database (NCDB) by plugging it as a pluggable database (PDB) into primary CDB (BOSTON). This method tremendously simplifies and speeds up the procedure to create a standby database without the need to set various initialization parameters or take backup or configure tnsnames.ora entries etc.

Monday, 28 November 2016

Emergency Monitoring in Oracle Enterprise Manager Cloud Control 12c

The Emergency Monitoring feature of Enterprise Manager Cloud Control 12c is an evolution of Memory Access Mode from Grid Control 11g, allowing you to access in-memory statistics. In EM12c you no longer need to explicitly enable memory access mode. Just navigate to the page and wait while it makes a diagnostic connection to the database and performs lightweight analysis. The screen contains information that will help analyze performance issues, even if the database is hung and you can't connect to the normal performance pages.

Saturday, 26 November 2016

PL/SQL Subtypes

We are always working with data and their datatypes. In the tables we constrain the data by their datatype. For instance, we create a price column of type NUMBER(4,2) telling the database to store a maximum of four (4) digits of which we want to use two (2) as the decimal digits. This is how we, in real life, use our prices. In PL/SQL we can anchor our variables to this type using the <TABLE>.%TYPE syntax. But what if there is no column to anchor to? We can also use the NUMBER(6,2) syntax to constrain the values possible for a certain variable.

Friday, 25 November 2016

Using the Oracle Database for an Analytic Warehouse


Data Warehouses have evolved since their heydays in the 1980s. Many people have given up on the dream of capturing and codifying into one place all the internal data for an organization and consider the task to be too difficult. In the quest to capture everything, they have focused almost exclusively on how to get data into a data warehouse through ETL processes. Instead, we believe the value lies not in storing data, but rather in organizing it, using it to calculate new measures, and facilitating its presentation to users. By focusing on results and uses, you can achieve what organizations truly care about: fast, deep, meaningful analyses.

Wednesday, 23 November 2016

Anatomy Of A SELECT Statement – Part 2: The Hierarchical Query Clause

But first, a definition.  A hierarchical query is a type of recursive query that returns a resultset that displays the natural relationship of data.  Or, if you like pretty mental pictures, hierarchical queries return datasets in which each record is (potentially) holding hands with the record before it.

Using a vanilla select query, you can find out from the EMP table all the staff for whom King is their manager. However, you will then need to run a second select statement to find out all the staff who report to them. Using a hierarchical query, we can find out, in one move, who reports to King, who reports to them and who reports to them. King is Jones’s manager; Jones is Scott’s manager; Scott is Adams’ manager…

Monday, 21 November 2016

Anatomy of a SELECT Statement – Part 1: The WITH Clause

Come, young one.  Come sit by the open fire; hold your cold hands to its warmth. Let me tell you a story.  Back when I was young, the world was a simpler place: we roamed the plains, hunted game with our spears – and Oracle Select statements consisted of only a SELECT, a FROM and, possibly, a WHERE.

Things done changed.

Monday, 14 November 2016

Oracle University Offers New Courses Covering Oracle Database 12c Release2

September was an exciting month for Oracle, as Oracle OpenWorld was in full swing, and new and exciting cloud services were announced to the market.  Oracle announced Oracle Database 12c Release 2 to the cloud. In addition, we also unveiled the  new cloud database offering, Oracle Database Exadata Express Cloud Service.

Friday, 11 November 2016

About functions, records and result cache

Functions are the instrument in the Oracle Database to execute code and return a value. Nothing new here, everybody who develops in PL/SQL on the database knows that.

Functions returning only one value

Let’s start with the easy stuff… the following function will retrieve a record from a table and concatenate the first and last name.

Thursday, 10 November 2016

Oracle Programming Basics: Overloading

It is probably a bad idea to define a word by using a bigger one, but I really can’t introduce the concept of overloading without briefly mentioning polymorphism. Polymorphism, in computer programming, is the ability of an object to interface in different ways depending on other, external criteria. And if that isn’t exactly clear, don’t worry about it; just know that overloading is a type of polymorphism. Store that fact somewhere in the back of your brain; you may never need it again.

Overloading is the act of creating multiple subprograms –  procedures or functions – with the same name.

Collections in 12c

In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your code from scratch, it’s easy enough to make the right choice, but if you are working with existing code and you need to make adjustment and/or you are not allowed to create type objects in the database, and still want to use the SQL power the database offers, since version 12c you can now use Associative Arrays to solve your problem.

Wednesday, 9 November 2016

Collections in Oracle Part 2


The syntax for defining a varray in SQL is like this:

CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type;

The syntax for defining a varray in PL/SQL is like this:

TYPE varray_type IS VARRAY(SIZE) OF  element_type;

After it has been defined in SQL the varray can be used as a column in the table definition, just like you would use one of the simple types.

Tuesday, 8 November 2016

Collections in Oracle Part 1

Collections are single-dimensioned lists of information, very similar to 3GL arrays. Much like ‘in-memory’ tables. Maybe that’s why they started out as PL/SQL Tables. The PL/SQL only version got renamed a couple of times. When first introduced in Oracle 7 they were called PL/SQL tables. In Oracle 8i they were renamed to Index By tables. Probably because of the index by clause in the syntax. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table did, but it can now be indexed by a VARCHAR2 as well.

Monday, 7 November 2016

Oracle GoldenGate 12c New Features – Part 1

The Oracle GoldenGate software package delivers low-impact, real-time data integration and transactional data replication across heterogeneous systems for continuous availability, zero-downtime migration, and business intelligence.

Oracle GoldenGate 12c empowers organizations to capture, route, transform, and deliver transactional data between heterogeneous databases in real time with minimal overhead.  In 12c, it is now available with new features for all major operating systems and databases.

Saturday, 5 November 2016

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1

Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible.
  • Syntax Made Simple
The pattern matching syntax includes a lot of options, which make it quite daunting at first. This section describes a very simplistic view of the syntax, allowing you to take your first steps. For a detailed description of the syntax, see the documentation

Friday, 4 November 2016

Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article describes how to connect to container databases (CDB) and pluggable databases (PDB).
  • Connecting to a Container Database (CDB)
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.

$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba