How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

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;  


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Understanding Oracle QUERY PLAN - A 10 minutes guide

    Confused about how to understand Oracle Query Execution Plan? This 10 minutes step by step primer is the first of a two part article that will teach you exactly the things you must know about Query Plan.

  • Oracle External Tables

    The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. This is a very convenient and fast method to retrieve data from flat files outside Oracle database.

  • How to find out Expected Time of Completion for an Oracle Query

    Too often we become impatient when Oracle Query executed by us does not seem to return any result. But Oracle (10g onwards) gives us an option to check how long a query will run, that is, to find out expected time of completion for a query.

  • Learn Oracle Server Architecture in 10 minutes

    Here is an easy to understand primer on Oracle architecture. Read this first to give yourself a head-start before you read more advanced articles on Oracle Server Architecture.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • Oracle Spool

    Oracle Spool for SQLplus command line utility, can be used in conjunction with OS to export data from Oracle to flat file.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • Oracle UTL_FILE

    The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.