Monday, 19 March 2018

Qualified Expressions in PL/SQL in Oracle Database 18c

Qualified expressions provide and alternative way to define the value of complex objects, which in some cases can make the code look neater.

◈ Syntax

The basic syntax for a qualified expression is as follows

Friday, 16 March 2018

Data Pump (expdp, impdp) Enhancements in Oracle Database 12c Release 2 (12.2)

This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 2 (12.2).

Some of these features are not listed in the expdp -help or impdp -help usage text.

1. Parallel Export/Import of Metadata

In previous releases the PARALLEL parameter was only relevant to the export/import of data, with all metadata processed serially. In Oracle 12.2 both metadata and data can be exported in parallel provided it is not a transportable tablespace operation.

Wednesday, 14 March 2018

External Tables and XMLTAG to Load XML Documents in Oracle Database 12c Release 2 (12.2)

This article describes how to use the XMLTAG clause of an external table to load XML fragments from XML documents in Oracle Database 12c Release 2 (12.2).

Monday, 12 March 2018

Partitioned External Tables in Oracle Database 12c Realease 2 (12.2)

Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise joins. With the exception of hash partitioning, many partitioning and subpartitioning strategies are supported with some restrictions.

Friday, 9 March 2018

Efficient Function Calls From SQL

This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.

◈ The Problem

Sometimes we require functions in the select list of a query. These may be built in functions, or functions we've coded ourselves. The default action is to call the function for each row returned by the query. If the function is deterministic, for the same input parameter signature it gives the same output, and the number of unique parameter signatures used is low compared to the number of rows processed, this can represent a lot of wasted effort.

Wednesday, 7 March 2018

How to Send a TDE Encrypted Backup to Someone Outside Your Organization

Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted.  If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?