Top 60 Oracle Blogs

Recent comments

Oracle Development

APEX 5.1 is Here!

It’s here! Without much fanfare APEX 5.1 became available for download on December 21, 2016! With all the holiday stuff going on I missed it until after the first of the year.

Here’s where you can download APEX 5.1 and find out more:

APEX 5.1 is a significant release and should be pretty stable considering the long beta program and early adopter programs it went through.

Major new features include:

Oracle 12c – UTL_CALL_STACK for Easier Debugging


Oracle has provided PL/SQL debug aids for a long time; perhaps your show currently uses one or more of the following

  • dbms_utility.format_call_stack
  • dbms_utility.format_error_backtrace
  • dbms_utility.format_error_stack

Oracle 12c adds the UTL_CALL_STACK package providing greater insight into the stack.

UTL_CALL_STACK includes the following functions

Clearing Clouds: Unraveling IaaS, PaaS, and SaaS

Cloud, Cloud, and More Cloud!

Cloud is upon us! Unless you’ve been living under a rock you must be aware that our industry is headed to the cloud; some of us are already there!

Many cloud services are available and more are coming every day. How can you make sense of the many “#ff0000;">?aaS” acronyms? In this article I’ll explain the differences between Cloud Service Models (IaaS, PaaS, and SaaS) and Cloud Deployment Options (public, private, and hybrid).

Cloud Service Models

Many, many acronyms come along with the cloud; here are three that are common:

  • IaaS  Infrastructure as a Service
  • PaaS  Platform as a Service
  • SaaS  Software as a Service


Infrastructure as a Service means that the cloud provider provides: Hardware, Operations. and maybe core operating systems.

Oracle 12.2 DBaaS Availability!

At Oracle Open World 2016 Larry Ellison introduced Oracle Database 12c Release 2 (12.2) and shared that it would be available first to Oracle Cloud customers.

Immediately after Open World 12.2 became available to people who subscribed to the Oracle Exadata Express Cloud; those of us on DBaaS needed to wait for a little while longer.

In the second week of November, Oracle released Oracle Database 12c Release 2 (12.2) to DBaaS (DataBase as a Service) customers. Here’s how it looks when creating an instance:


SQL Developer and Oracle DBaaS

Connecting to Oracle DBaaS from SQL Developer


So, you have access to an Oracle DBaaS instance. How do you connect so that you can run use SQL Developer’s navigator or execute SQL and PL/SQL scripts? An earlier post showed how easy it is to create an Oracle DBaaS instance. This article will show you how to connect to DBaaS from SQL Developer.

When a DBaaS instance is created it is necessary to supply a private/public key pair to enable more-secure access via SSH (Secure Shell). By adjusting the DBaaS properties, you can also expose the CDB and PDB using the IP address without the protection of SSH (probably not a good idea for production use).

SSH Public Key

Oracle’s DBaaS Is Easy


Oracle has been king of the database hill for many years. Now, they’re extending that dominance to the cloud. Oracle DataBase as a Service (DBaaS) uses the Platform as a Service (PaaS) model to enable deployment and management of Oracle database instances in the cloud.

Using Oracle’s DataBase as a Service (DBaaS) is quick and easy. In the example below I create and deploy an Oracle SE instance in about thirty minutes; about twenty of that was waiting for the system to complete provisioning. Have you ever been able to create a database and built the server space it required so simply? Once you have Oracle’s DBaaS, instance creation and deployment can be as easy as following a wizard-based process; no forms to fill out from you operations people and no hardware to purchase/allocate.

Creating a New Service

Oracle 12c – Invisible Columns!

Oracle 12c – Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE and ALTER TABLE statements.

#ff0000;">What the?!?!?!?!

Suppose you have code that uses “SELECT *” (no, really this sometimes occurs) and you need to add a column or columns? Oops! Code using “SELECT *” now breaks as does code using INSERT statements without a column list. (I know, ALL of your system’s code uses explicit column name lists in SELECT and INSERT ; this does not apply to your shop… (please excuse the sarcasm))

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

Oracle 12c now provides a “white list” capability for procedure/function execution by means of a new PL/SQL ACCESSIBLE BY clause used to control access. ACCESSIBLE by specifies which objects are able to reference a PL/SQL object directly (sometimes called a “white list”). The ACCESSIBLE BY clause may be added to packages, procedures, functions, and types allowing an additional layer of security. Even if by some accident an intruder obtains permissions to use stored PL/SQL; the ACCESSIBLE BY rules will not allow any access that violates the specified rules.

Example Coding

Here’s an Example of ACCESSIBLE BY in a simple function:


Oracle 12c – PL/SQL in SELECT

It is often useful to execute PL/SQL functions as part of an SQL SELECT statement (or other DML). When SQL calls PL/SQL a “context switch” occurs as execution moves between SQL execution and PL/SQL execution. In Oracle Database 12c Oracle has given us two tools to help reduce the impact of this context switching. The first is the ability to add a local function definition to the SELECT via the WITH clause, the second is to flag a function using PRAGMA UDF so that the compiler and optimizer will know it is to be used from SQL. Both options can measurably improve performance if used correctly.

To achieve maximum performance gains the PL/SQL function involved should not call other PL/SQL procedures and/or PL/SQL functions nor should PRAGMA UDF functions be called from “normal” PL/SQL (not inside SQL).

Oracle 12c SQL – Using JSON

JSON Is Part of Oracle 12c

One of the more-exciting features (at least to me) of Oracle 12c is the support for JSON now built into the database.

What is JSON?
JSON (JavaScript Object Notation) is a language-independent open data format (see for more). JSON stores text in name-value pairs. Originally usedin JavaScript, JSON is now also in: Java, R, .NET, PHP, Python, Node.js, and Oracle. JSON is most-often used for data interchange but is frequently used to pass data to and from REST-style web services. Increasingly JSON is becoming favored for big-data persistence.