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


set echo off
set feedback off
set termout off
set heading off
set headsep off
set underline off
set linesize 2000
set pagesize 10000
set trimspool on
set embedded on
set colsep '	'

connect scott/tiger@orcl

column cdcdate noprint

column empno format 9999999999 heading "EMP_NUM"
column deptno format 9999999999 heading "DEPT_NUM"
column ename format A60 heading "EMP_NAME"
column dname format A20 heading "DEPT_NAME"
column hiredate format 'YYYYMMDD' heading "HIRE_DATE"
column sal format 999999999.99 heading "SALARY"

spool C:\Output_Files\Employee_&cdcdate..txt

emp.empno EMP_NUM,
emp.deptno DEPT_NUM,
emp.ename EMP_NAME,
dept.dname DEPT_NAME,
TO_CHAR( emp.hiredate, 'YYYYMMDD' ) HIRE_DATE,
emp,salary SALARY
emp, dept
WHERE emp.deptno = dept.deptno
AND TO_CHAR( emp.hiredate, 'YYYYMMDD' ) = TO_CHAR( SYSDATE - 1, 'YYYYMMDD' )
spool off
set feedback on
set heading on

@REM Windows Batch Script to Upload Data file to FTP location

@echo off
@REM Connect to SQLPlus, execute SQL & generate Spool file
sqlplus /nolog @C:\Scripts\INTERFACE.txt

@REM Generate Script file for WinSCP for automation
@echo off
SET File=Employee
SET Dir=C:\Output_Files\
SET mydate=%date:~10,4%%date:~4,2%%date:~7,2%
@REM SET mydate=%date:~10,4%%date:~7,2%%date:~4,2%
@REM echo %mydate%
SET Filename=%File%_%mydate%.txt
SET FilenameNew=%File%_%mydate%_New.txt
@REM echo %Filename%
@REM Remove Blank Lines from Data File
cd "C:\Output_Files\"
For /F "tokens=* delims=" %%A in (%Filename%) Do Echo %%A >> %FilenameNew%
move %FilenameNew% %Filename%
echo # Automatically abort script on errors > C:\Scripts\file_upload_sftp.txt
echo option batch abort  >> C:\Scripts\file_upload_sftp.txt
echo # Disable overwrite confirmations that conflict with the previous  >> C:\Scripts\file_upload_sftp.txt
echo option confirm off >> C:\Scripts\file_upload_sftp.txt
echo # Connect using a password >> C:\Scripts\file_upload_sftp.txt
echo open >> C:\Scripts\file_upload_sftp.txt
echo # Change the remote directory >> C:\Scripts\file_upload_sftp.txt
echo lcd %Dir% >> C:\Scripts\file_upload_sftp.txt
echo put %Filename% >> C:\Scripts\file_upload_sftp.txt
echo # Disconnect >> C:\Scripts\file_upload_sftp.txt
echo close >> C:\Scripts\file_upload_sftp.txt
echo # Exit WinSCP >> C:\Scripts\file_upload_sftp.txt
echo exit >> C:\Scripts\file_upload_sftp.txt
@REM Run WinSCP to upload file
"C:\Program Files\WinSCP\" /ini="C:\Scripts\WinSCP.ini" /script="C:\Scripts\file_upload_sftp.txt" /log="C:\Scripts\ftp.log"

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.

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

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

  • 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!

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

  • 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!

  • 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 Spool

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

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

  • Understanding Oracle QUERY PLAN - Part2 (Exploring SQL Joins)

    This is the second part of the article Understanding Oracle Query Plan. In this part we will deal with SQL Joins.

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