Different Methods to move data from Oracle to External File

Using Oracle DATA PUMP

Using ORACLE_DATAPUMP access driver we can push data in database tables into external flat files. In the previous article on Oracle External Tables we have seen the default driver ORACLE_LOADER to read from external files. Now in this article we will learn how to push data to flat files using the access driver ORACLE_DATAPUMP.

Here goes the example:

CREATE TABLE emp_ext 
ORGANIZATION EXTERNAL 
( 
TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_tab_dir  
LOCATION ( 'emp.dmp' ) 
) 
AS SELECT * FROM EMP;

Here the external file emp.dmp will contain all the data of the table EMP. We can use the same file generated here as source and then create an oracle external table to retrieve data into some other Oracle system.

Using UTL_FILE

Another method to read and write external files from oracle is to use the Oracle supplied UTL_FILE package.

Using SQLPLUS SPOOL command

Using the SPOOL sqlplus command we can generate output files in the client machine.

For example:

We create a file in C:\External_Tables named emp_query.sql with the following saved query.

SELECT EMPNO ||',' || ENAME || ',' || SAL || ',' || COMM FROM EMP;

SQL> CONN SCOTT/TIGER@ORCL 
Connected. 
SQL> SET FEEDBACK OFF HEADING OFF ECHO OFF 
SQL> SPOOL C:\External_Tables\emp.csv 
SQL> @C:\External_Tables\emp_query.sql 
SQL> SPOOL OFF

To view the content of the file from SQLPLUS, type..

SQL> host type C:\External_Tables\emp.csv

Oracle to XML file

Now let us see how can we output the table data in the form of xml file format. The following code will display the xml format of the table data. Now the easy way to create an xml file is to use the Oracle SPOOL sqlplus command to spool the output in the client machine.

Example 1:

SELECT DBMS_XMLGEN.GETXML( 
  'SELECT empno, ename, deptno FROM emp WHERE deptno = 10' 
  , 0 
  ) FROM DUAL;

Example 2:

SELECT DBMS_XMLGEN.GETXML(  
 'SELECT dept.*'  
||'      ,CURSOR('  
||'              SELECT emp.*'  
||'              FROM emp' 
||'              WHERE emp.deptno = dept.deptno' 
||'             ) emp_list' 
||' FROM dept') xmldata 
FROM dual;