External Tables

This is a feature I use a lot…

An external table in Oracle is a table that its structure is defined in the data dictionary, but its content is taken from a standalone file, and is not stored inside the database. This feature is very useful as part of an ETL process, where you need to load the content of some files into the database.

The external file must be located in the file system of the database server machine (or at least in a directory that it can access). This is the major drawback I see in this feature with respect to its predecessor, SQL*Loader (which loads files located at the client machine). But the advantages are significantly superior to the disadvantages.

The definition of the external table describes how to locate, parse and process the file: Continue reading “External Tables”

Literals

It is possible to specify a date value as a literal, like that:

DATE '2013-08-29'

This instantiates a date constant, which is equivalent to the result of

to_date('29/08/2013','dd/mm/yyyy')

The date format in this case is always yyyy-mm-dd, and is not influenced by any parameter (the default format of the to_date function is defined by the NLS_DATE_FORMAT parameter).

For example, to find all the employees that were hired during August 2013 we can use the following query:

select * 
from employees 
where hire_date >= date'2013-08-01' 
  and hire_date < date'2013-09-01';

To specify a value that contain also a time part as a literal, you can use:

TIMESTAMP '2013-08-29 13:45:00'

The format is always yyyy-mm-dd hh24:mi:ss (fractions of seconds may also be specified).

The MERGE statement

The SQL statement MERGE (available from version 9i) provides the functionality of “INSERT or UPDATE” in a single statement.

The basic syntax is:

MERGE INTO <target>
USING <source>
ON (<join conditions>)
WHEN MATCHED THEN UPDATE <SET clause>
WHEN NOT MATCHED THEN INSERT (<column list>) VALUES (<expression list>);

where:

  • <target> is the table that we’d like to insert new records into and/or update existing records in
  • <source> is usually some SQL query (it may also be simply a table)
  • <join conditions> specify the relationship between the source and the target – these conditions determine whether a row from the source exists in the target or not

Each row from the source is looked up in the target table, using the conditions specified in the ON clause. If the row exists in the target table, then the WHEN MATCHED clause is executed – the target row is updated. If the row does not exist in the target table, then the WHEN NOT MATCHED clause is executed – a new row is inserted into the target table.

Let’s take an example. Continue reading “The MERGE statement”

Course Addendum

I lectured about Oracle SQL and PL/SQL in a course lately. As always, some issues that had not been part of the planned scope were raised and discussed, so I thought to write short posts about some of them, for the benefit of the participants and everyone else that may find it relevant.

These issues are:

Lateral Inline Views

Lateral Inline Views – an official feature of Oracle 12c (was hidden/unsupported/undocumented in 11g) – lets you refer within an inline view to tables that appear to its left in the FROM clause.

Let’s take an example (a simplification of a real-life use case I handled recently). We have a SCHEDULES table, including for each repeatable task the time range in which it should be executed, and the frequency:

create table schedules (
  name            varchar2(100) not null primary key,
  start_time      date,
  end_time        date, 
  repeat_interval interval day to second
);

Let’s fill it with 3 records: Continue reading “Lateral Inline Views”

The FIRST and LAST Aggregate Functions

One of my favorite aggregate functions in Oracle is FIRST (and of course its counterpart, LAST; ok, so two of my favorite aggregate functions).

The very common aggregate functions MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.

The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group. Continue reading “The FIRST and LAST Aggregate Functions”

Creating an Index on a Static Table Referenced by an Active Table

We have a parent-child pair of tables with a foreign key constraint between them, and we need to add an index to the parent table, while the application is active.

The parent table is static during the creation of the index (no DML on it), but lots of DML statements are done on the child table. The parent table is relatively big, so the index creation takes a relatively significant time.

Continue reading “Creating an Index on a Static Table Referenced by an Active Table”