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"?>
<rowset>
  <row>
    <owner>SYS</owner>
    <object_name>I...</object_name>
...
...
</row>
</rowset>
 
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. 8-)