DW/ BI

Unloading Data from Oracle Table

This is a generic and popular requirement for any Database/ Oracle Developer to know wow 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.

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

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s