Oracle Spool

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