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. 😎
