Code Example: batch, sqlplus

=========================
General
=========================
Code example
Overall - read data from Database
Generate files using oracle UTL_FILE
FTP files from oracle server to customer serer
Update records in Database

The main is main_send_files.bat

=========================
Code
=========================

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

main_send_files.bat
=======================================

REM set Paths
SET HOME_DIR=D:\CUST_APU_Reports\Files
SET OLD_DIR=D:\CUST_APU_Reports\OldFiles

REM Delete old temp file
IF EXIST DELnewCMD.ini DEL DELnewCMD.ini
IF EXIST del_files.txt DEL del_files.txt
IF EXIST sftp_output.log DEL sftp_output.log 

REM get date
for /f "tokens=2,3,4 delims=/ " %%i in ('date/t') do (set mm=%%i) & (set dd=%%j) & (set yyyy=%%k)
set RUN_DATE=%yyyy%%mm%%dd%

REM Override default Time
REM SET RUN_DATE='20170714'

echo %RUN_DATE% >> cust_log.txt

for /f "tokens=1,2 delims=: " %%i in ('time /t') do (set hh=%%i) & (set mm=%%j)
REM get time
set RUN_TIME=%hh%:%mm%
echo %RUN_TIME% >> cust_log.txt

echo #### >> cust_log.txt
echo Activate database package >> cust_log.txt
echo #### >> cust_log.txt
sqlplus user/pass@oraint @activate_report_new.sql %RUN_DATE%
IF NOT ERRORLEVEL 0 goto :ERROR_DB

:BRING_THE_FILES
echo before FTP >> cust_log.txt
CALL SFTPCMD2 > sftp_output.log 
type sftp_output.log >> cust_log.txt
echo Finished FTP successfully >> cust_log.txt

dir /B D:\cust_APU_Reports\Files\output*.csv /O:d >> del_files.txt
echo cd /software/oracle/admin/igt/utl_file >> DELNEWCMD.ini
for /F %%i in (del_files.txt) do (@echo rm %%i) >> delnewcmd.ini
echo bye >> DELnewCMD.ini
CALL SFTPCMD3 > sftp_output.log 
type sftp_output.log >> cust_log.txt
if NOT ERRORLEVEL 0 GOTO :ERROR_DELETING

echo send the files by sftp to the customer (10.92.40.20)>> cust_log.txt
CALL SFTPCMD SFTPCMD.ini > sftp_output.log 
type sftp_output.log >> cust_log.txt

REM echo send the files by sftp to the customer >> cust_log.txt
REM CALL SFTPCMD4 SFTPCMD4.ini > sftp_output.log 
REM type sftp_output.log >> cust_log.txt

IF NOT ERRORLEVEL 0 GOTO :ERROR_SFTP
echo Rename the sent CSV files to old directory >> cust_log.txt
for /F  %%i in (del_files.txt) do (move %HOME_DIR%\%%i %OLD_DIR%\%%i)
IF NOT ERRORLEVEL 0 GOTO :ERROR_RENAME
echo process finish succefully >> cust_log.txt
goto FINISH
:ERROR_DB
 echo Error in the DAILY_REPORTS package. >> cust_log.txt
REM REM send mail
:ERROR_DELETING
 echo Errors deleting from the cgw! >> cust_log.txt
REM REM send mail
 goto FINISH
:ERROR_SFTP
 echo Errors SFTP the files to CUST >> cust_log.txt
REM REM send mail
 goto FINISH
:ERROR_RENAME
 echo Error moving the files to the oldDir directory >> cust_log.txt
REM send mail
 goto FINISH
:FINISH

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

SFTPCMD.bat

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

sftp -b SFTPCMD.ini user@10.10.10.10

SFTPCMD.ini

lcd D:\CUST_APU_Reports\Files

cd ../VHE

mput CUST_APU*.csv

bye

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

SFTPCMD2.bat

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

sftp -b DWHNEWCMD.ini user@20.20.20.20

dwhnewcmd.ini

lcd D:\CUST_APU_Reports\Files

cd /software/oracle/admin/igt/utl_file

mget output*.csv

bye

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

SFTPCMD3.bat

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

sftp -b DELnewCMD.ini user@30.30.30.30

DELNEWCMD.ini

cd /software/oracle/admin/igt/utl_file 

rm CUST_APU_BHCA_20170716.csv

rm CUST_APU_BHSM_20170716.csv

bye  

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

activate_report_new.sql

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

exec DAILY_REPORTS.CUST_MAIN(&1);

exit;