Data Spooling & Export from DB

Data Spooling from DB Table to Flat File

This Article is to pin down simple commands to spool data from a DB table to a flat file from SQL*Plus.

set echo off;

set feedback off;

set linesize 100;

set pagesize 0;

set sqlprompt '';

set trimspool on;

spool d:\FlatFileOut.out;

Select * from emp where rownum <100;

spool off;

set echo on;

set feedback on;

set sqlprompt 'SQL>'; 

==========================

Steps to spool Package from DB table 

set echo off;

set feedback off;

set pages 0;

set heading off;

set sqlprompt '';

set linesize 180;

spool d:\<File_name>;

select text from dba_source where name like 'XX_EXTRACT_PKG';

spool off;

set echo on;

set feedback on;

set sqlprompt 'SQL>'; 

===========================

Data Extract from DB Table to Excel File from UNIX

Step1 -> Place the .sql file in UNIX box - with help of any FTP tool

Step2 -> Open UNIX box to the same path

Step3 -> Set the environment to enter into sql plus

Step4 -> Type <sqlplus>

alter session set current_schema = apps;

BEGIN             

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(101);                

END;

Step5 -> Run the script :

@<script_name.sql>

Step6 -> Connect to FTP tool and download the file