Oracle 10g SQL New Features

were introduced in Oracle 10g Release 1 and are an enhancement of the external table features introduced with Oracle 9i.

Before you get too excited though it is important to note that they are not full read/write tables like a normal Oracle database table. They can only be written to at the time they are created using a 'create table table as select ...' SQL command.

So what use are they? Well they're really designed for data warehouses - to enable data to be extracted from the transaction database and then loaded into the datawarehouse. They could also be used for logical backups, although the data pump utilities would be better for that.

One other point to note before we see an example is that Oracle 10g now has two access drivers for external tables: oracle_loader and oracle_datapump. The oracle_loader driver was the only one available in Oracle 9i and if this is used the external table is read only. To be writeable, the oracle_datapump driver must be specified when the table is created.

Writeable External Table Example

Let's look at an example of the sql commands needed to create a writeable external table assuming the directory has already been defined in Oracle and read/write access granted to the relevant user(s).

SQL>CONNECT hr/hr;
SQL>CREATE TABLE new_sales
2 (emp_id, emp_name, product_code, product_name, cust_name,
3 sale_date, sale_amt)
4 ORGANIZATION EXTERNAL
5 (
6 TYPE oracle_datapump
7 DEFAULT_DIRECTORY staging_data
8    LOCATION ('new_sales.dp')
9 )
10 AS
  11 SELECT emp_id, emp_name, product_id, product_code
12 , product_name, cust_id, cust_name, sale_date, sale_amt
13 FROM sales JOIN product USING (product_id)
14 JOIN customer USING (cust_id)
  15 WHERE TRUNC (SYSDATE) > TRUNC(SYSDATE-1);

If you get the following error when creating the external table make sure that the directory has already been created in the file system.
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EMP_EXP_508_2248.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19

Whilst Oracle automatically creates the file to hold the external table it does not create the directory in which the file resides.

Once the external table has been created you can run SQL queries against it as if it were a normal database table, but you can't run insert, update or delete statements!

Table/Database Restore Points and Flashback Queries

Another intersting and useful new feature in Oracle 10g (release 2) is the ability to create named restore points. These are similar to savepoints but unlike savepoints are not limited to a particular transaction.

They are used in conjunction with the database flash recovery area to rollback all transactions on a table (or database wide) to the point in time when the restore point was created.

Restore points are created using the SQL command CREATE RESTORE POINT and by default are stored in the database for 7 days or until the maxinum number of restore points is reached at which time the oldest one is dropped automatically by Oracle. They can also be dropped manually using the SQL command DROP RESTORE POINT. To see what restore points have been created in the database you can query the system view v$restore_point.

To revert to a restore point it's simply a matter of issuing the command FLASHBACK TABLE <table_name> TO <name_of_restore_point>.

Restore Points and Flashback Query Examples

Let's look at an example using the hr schema that is created in the sample
database.

First create a restore point. SQL>CREATE RESTORE POINT one;

Next, query the employees table so that we will know whether or not the changes have been undone.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randall

Now change some data

SQL> UPDATE employees SET first_name='Randy'
2 WHERE employee_id=191;
SQL>COMMIT;

re-query just to be sure.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randy

Next wait a couple of minutes before reverting to our restore point

SQL>FLASHBACK TABLE employees TO RESTORE POINT one;

Flashback complete

(N.B. row movement has to be enabled before a table can be flashed back. To do this just issue the SQL command 'ALTER TABLE employees ENABLE ROW MOVEMENT')

Re-query again

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randall

The change we made has been undone, let's see if we can redo it by flashing forwards in time.

SQL>FLASHBACK TABLE employees TO TIMESATMP(SYSTIMESTAMP - INTERVAL '2' MINUTE);

Flashback complete

Let's see what data we have in our Oracle database now.

SQL>SELECT first_name FROM employees WHERE employee_id=191;

FIRST_NAME
-------------------
Randy

Magnificent! We went back to the time before we'd made any changes then came forwards to the time after we'd made the change. Could be a great tool for playing tricks on your colleagues!

You can also use the Oracle SQL flashback commands and restore points to rewind the whole database - just replace table with database (the database must be mounted but not open for this).

There are two more things you can do with flashbacks:

you can see all the versions of the data in a table (or particular column) that existed between two timestamps or system change numbers (scn)
you can restore tables that have been dropped

Let's look at an example of each of these.

Querying versions of data

To do this we need to use the new flashback query clause in the Oracle SQL SELECT statement.

In this example we display all the versions that we have of employee #142s first name as well as the start and end tiime of each version (using the pseudo columns versions_starttime and versions_endtime). Notice that the results are stored in reverse chronological order.

SELECT versions_starttime,versions_endtime,first_name FROM employees VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE employee_id=143
/

VERSIONS_STARTTIME   VERSIONS_ENDTIME     FIRST_NAME
-------------------- -------------------- --------------------
19-MAY-09 16.13.44                      sandy
19-MAY-09 16.13.23   19-MAY-09 16.13.44   Randy
19-MAY-09 16.13.02   19-MAY-09 16.13.23   Randall
                     19-MAY-09 16.13.02   Mandy

We can replace timestamp with SCN and/or use the pseudo columns versions_startscn and versions_endscn as well.

Retrieving dropped tables

This is achieved with an alternative clause of the SQL command FLASHBACK TABLE and the recycle bin introduced with Oracle 10g release 1.

sql>DROP TABLE employees; -- table goes into recycle bin
sql>FLASHBACK TABLE employees TO BEFORE DROP; -- table restored

One thing to note is that the table's extents are not released until the table is purged from the recycle bin which can be done manually and is done automatically by Oracle when more space is required in the database.

Regular Expression Support

Oracle 10g release 1 added support for the use of POSIX regular expressions in both SQL and PL/SQL. Follow this link for an introduction to the use of regular expressions in Oracle 10g PL/SQL - they operate in much the same way in SQL as they do in PL/SQL.

Support for common Perl-style regular expressions that do not conflict with the POSIX standard were added in Oracle 10g release 2.

One great use for regular expressions in SQL is in validating data by defining column constraints (of course data validation should also be done by the application but adding constraints stops bad data getting in via the "back door").

For example to ensure telephone numbers are entered in the correct format for New Zealand you could use a regular expression like this "^$0[34679]$ \d{3} \d{4}$". That pattern would match numbers such as '(09) 415 0981', '(03) 216 1098','(07) 111 1234'.

Here's an example of how you could use this in Oracle 10g and above:

CREATE TABLE contacts
(first_name VARCHAR2(30)
,last_name VARCHAR2(30)
,phone VARCHAR2(13) CHECK (REGEXP_LIKE (PHONE,'^$0[34679]$ \d{3} \d{4}$'))
);
See Oracle 10g SQL New Features part 2 for more new features.

Full details of Oracle 10g SQL syntax are available in the Oracle database documentation.