Oracle 12c: Export by View

A small enhancement, but what a golden nugget! At least that’s what I think. 😉

It is now possible in Oracle 12c to export data via a view. I my realm, Oracle XMLDB, that can be an interesting feature to export stored XML content in a relational format and import it, for example, in a relational table/environment. Let me show you how.

Lets create an XMLTYPE table and insert some XML into it:

SQL> create table xmltype_table of xmltype;

Table created.

SQL> desc xmltype_table
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
TABLE of PUBLIC.XMLTYPE STORAGE BINARY

SQL> select xmltype(cursor(select * from all_objects)) as XMLCONTENT
  2    from dual;

XMLCONTENT
--------------------------------------------------------------------------------
< ?xml version="1.0"?>

  
    SYS
    I...
...
...



SQL> select count(*) from xmltype_table;

  COUNT(*)
----------
	 0

SQL> insert into xmltype_table (object_value)
  2  select xmltype(cursor(select * from all_objects))
  3    from dual;

1 row created.

SQL> commit;

Commit Complete.

Now if you would, in SQL*Plus, set “set long” to a very big value, you would see the canonical XML document with all information of ALL_OBJECTS where your account has access to. To demonstrate the export via view functionality, in this example, we will have to create a view on top of this XML content in the table XMLTYPE_TABLE. This can be done, for instance, via creating a view via the following method.

 
SQL> select xt1.owner_name as MY_OWNER
  2	  , xt1.object_name as MY_OBJECT
  3    from xmltype_table tab1
  4	  , xmltable('/ROWSET/ROW'
  5		     passing tab1.object_value
  6		     columns
  7		       owner_name  VARCHAR2(30) path 'OWNER',
  8		       object_name VARCHAR2(50) path 'OBJECT_NAME'
  9		    ) xt1
 10  where rownum < = 10
 11 ;

MY_OWNER		       MY_OBJECT
------------------------------ --------------------------------------------------
SYS			       ICOL$
SYS			       I_CDEF3
SYS			       TS$
SYS			       CDEF$
SYS			       I_FILE#_BLOCK#
SYS			       I_FILE2
SYS			       I_OBJ5
SYS			       I_OBJ1
SYS			       I_OBJ4
SYS			       I_USER2

10 rows selected.

Before we can export the data via a view, we have to create a DIRECTORY and (of course) the view...

SQL> create directory XMLDMP as '/tmp';

Directory created.

SQL> create view XMLTYPE_VIEW
  2  as
  3  select xt1.owner_name as MY_OWNER
  4  	  , xt1.object_name as MY_OBJECT
  5    from xmltype_table tab1
  6  	  , xmltable('/ROWSET/ROW'
  7  		     passing tab1.object_value
  8  		     columns
  9  		       owner_name VARCHAR2(30) path 'OWNER',
 10  		       object_name VARCHAR2(50) path 'OBJECT_NAME'
 11  		    ) xt1
 12   where rownum < = 10
 13  ;

View created.

SQL> desc XMLTYPE_VIEW
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 MY_OWNER							VARCHAR2(30)
 MY_OBJECT							VARCHAR2(50)

Now outside the database we can use the datapump export utility to export the data via the just created view.

[oracle@orcl12 tmp]$ expdp test/test DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp VIEWS_AS_TABLES=TEST."XMLTYPE_VIEW" REUSE_DUMPFILES=Y

Export: Release 12.1.0.1.0 - Production on Wed Jun 26 15:14:23 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp VIEWS_AS_TABLES=TEST.XMLTYPE_VIEW REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "TEST"."XMLTYPE_VIEW"                       5.632 KB      10 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /tmp/xmltype_table.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 26 15:14:36 2013 elapsed 0 00:00:11

The “VIEW_AS_TABLES” signals expdp that:

VIEWS_AS_TABLES

Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

Note that in network import mode, a table name may be appended to the view name.

To demonstrate that when you import the dumpfile again, that it delivers a relational table, do the following:

SQL> select tname as NAME
  2	  , tabtype as TYPE
  3  from tab
  4 ;

NAME			       TYPE
------------------------------ -------
XMLTYPE_VIEW		       VIEW
XMLTYPE_TABLE		       TABLE

On the command prompt:

oracle@orcl12 ~]$ cd /tmp
[oracle@orcl12 tmp]$ ls -ltra
totaal 200
...
...
drwxrwxrwt 16 root   root       4096 jun 26 15:14 .
-rw-r-----  1 oracle oinstall 114688 jun 26 15:14 xmltype_table.dmp
-rw-r--r--  1 oracle oinstall   1097 jun 26 15:14 export.log

[oracle@orcl tmp]$ impdp test/test DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp REMAP_TABLE=XMLTYPE_VIEW:RELATIONAL_TABLE

Import: Release 12.1.0.1.0 - Production on Wed Jun 26 15:33:00 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp REMAP_TABLE=XMLTYPE_VIEW:RELATIONAL_TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "TEST"."RELATIONAL_TABLE"                   5.632 KB      10 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Wed Jun 26 15:33:03 2013 elapsed 0 00:00:02

Now checking via SQL*Plus it shows a newly created table with name RELATIONAL_TABLE, with the same definition as it’s source (the view XMLTYPE_VIEW) and with 10 records inserted.

SQL> select tname as NAME
  2	  , tabtype as TYPE
  3  from tab;

NAME			       TYPE
------------------------------ -------
RELATIONAL_TABLE	       TABLE
XMLTYPE_VIEW		       VIEW
XMLTYPE_TABLE		       TABLE

SQL> select count(*) from relational_table;

  COUNT(*)
----------
	10

SQL> select * from relational_table;

MY_OWNER		       MY_OBJECT
------------------------------ --------------------------------------------------
SYS			       ICOL$
SYS			       I_CDEF3
SYS			       TS$
SYS			       CDEF$
SYS			       I_FILE#_BLOCK#
SYS			       I_FILE2
SYS			       I_OBJ5
SYS			       I_OBJ1
SYS			       I_OBJ4
SYS			       I_USER2

10 rows selected.

It is probably good to mention that the Oracle XMLDB functionality (based on W3C XML standards) is an Oracle kernel build-in, based on C code, and therefore out performs most outside, mainly Java based, solutions.

Just so you know. 😎