Oracle

Oracle Spool

Saurav Mitra

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


--INTERFACE.txt

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
SELECT TO_CHAR( SYSDATE, 'YYYYMMDD' ) cdcdate FROM dual;

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

SELECT
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
FROM
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
exit


@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 ftp://ftpuser@ftpsite.xyz.com >> 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\winscp.com" /ini="C:\Scripts\WinSCP.ini" /script="C:\Scripts\File_upload_sftp.txt" /log="C:\Scripts\ftp.log"
 
exit