Oracle Database 12c: Export by View

0

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-)

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance.He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

Leave a Reply