Code Example. Generate xls file from DB.(batch, sqlplus)

====================
General
====================
Generate xls file from database.

====================
Files List
====================
BRING_FROM_DB.bat
activate_report.sql
GenerateReport.sql

DELDWHCMD.ini
DWHCMD.ini
SFTPCMD2.bat
SFTPCMD3.bat

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

BRING_FROM_DB.bat
REM set Paths

SET HOME_DIR=D:\Report\files
SET OLD_DIR=D:\Report\Oldfiles

REM Delete old temp file

IF EXIST DELDWHCMD.ini DEL DELDWHCMD.ini
IF EXIST del_files.txt DEL del_files.txt

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%
set EMAIL_RUN_DATE=%dd%/%mm%/%yyyy%
echo %RUN_DATE% >> TapFilesLog.txt

REM get time

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

REM echo #### >> TapFilesLog.txt
echo Activate database package: >> TapFilesLog.txt
REM echo #### >> TapFilesLog.txt
sqlplus user/pass@orainst @activate_report.sql %RUN_DATE%
REM for debug: 20090405
REM append spool file into log
type procedure_result.txt >> TapFilesLog.txt

IF EXIST procedure_result.txt DEL procedure_result.txt
IF NOT ERRORLEVEL 0 goto :ERROR_DB

:BRING_THE_FILES
echo before FTP >> TapFilesLog.txt
CALL SFTPCMD2
echo Finished FTP successfully >> TapFilesLog.txt
dir /B D:\report\files\*.xls /O:d >> del_files.txt
echo cd /software/oracle/admin/igt/utl_file >> DELDWHCMD.ini

for /F %%i in (del_files.txt) do (@echo rm %%i) >> DELDWHCMD.ini
echo bye >> DELDWHCMD.ini
echo delete files on DWH server >> TapFilesLog.txt
CALL SFTPCMD3
if NOT ERRORLEVEL 0 GOTO :ERROR_DELETING
echo send the files by email to the customer >> TapFilesLog.txt

REM ### START DEBUG ###
REM for /F %%i in (del_files.txt) do (blat MailBody.txt -to me@gmail.com -bcc me@starhome.com -from me@starhome.com -subject "Starhome Tap File Report 
%EMAIL_RUN_DATE%" -attach files\%%i)
REM ### END DEBUG ###

if NOT ERRORLEVEL 0 GOTO :ERROR_SFTP
echo Rename the sent XLS files to old directory >> TapFilesLog.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 >> TapFilesLog.txt
goto FINISH
:ERROR_DB
 echo Error running the procedure "monthly_reports.TapMonthlyReport" in the database please consult MIS ! >> TapFilesLog.txt
REM REM send mail
:ERROR_DELETING
 echo Errors deleting from the DWH! >> TapFilesLog.txt
REM REM send mail
 goto FINISH
:ERROR_SFTP
 echo Errors Sending the report to customer by mail >> TapFilesLog.txt
REM REM send mail
 goto FINISH
:ERROR_RENAME
 echo Errors moving the files to the oldDir directory >> TapFilesLog.txt
REM REM send mail
 goto FINISH
:FINISH
REM pause

Report.sql
  PROCEDURE MonthlyReport(pDateOfRun VARCHAR2) IS
    l_cursor     INTEGER := DBMS_SQL.open_cursor;
    vDirectory   VARCHAR2(200) := 'UTL_FILE_DIR';
    vFname       VARCHAR2(200) := '';
    v_start_date VARCHAR2(8);
    v_end_date   VARCHAR2(8);
    
  BEGIN
    v_start_date := to_char(trunc(trunc(TO_DATE(pDateOfRun, 'YYYYMMDD'),'MM') - 1, 'MM'), 'YYYYMMDD');                                        
    v_end_date   := to_char(trunc(TO_DATE(pDateOfRun, 'YYYYMMDD'), 'MM') - 1,'YYYYMMDD');
    vFname := 'TAP_FILE_REPORT_' || v_start_date || '_' || v_end_date ||'.xls';              
  
    DBMS_SQL.parse(l_cursor,
                   'SELECT T.vpmn AS VPMN,U.hpmn_name AS HPMN ,T.seq_num AS SEQ_NUM,'||
                   'T.date_file_created,' ||
                   'T.first_call, T.last_call, T.num_of_records AS NUM_OF_RECORDS ' ||
                    'FROM TAP_FILE_CREATED T,EXT_TAP_CUSTOMER U ' ||
                    'WHERE T.customer_id = U.customer_id ' ||
                    'AND T.customer_id IN (''026'',''005'') ' ||
                    'AND T.date_of_call>= ''' || v_start_date ||
                    ''' AND T.date_of_call<= ''' || v_end_date ||
                    ''' ORDER BY U.hpmn_name, T.vpmn, T.seq_num',
                   DBMS_SQL.native);
   
    XML_SPREADSHEET.abfrage(p_cursor => l_cursor, p_path => vDirectory, p_filename => vFname, p_sheetname => 'Tap File Report');
                            
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END MonthlyReport;

activate_report.sql

spool D:\Report\procedure_result.txt

exec monthly_reports.MonthlyReport(&1);

spool off

exit;

DELDWHCMD.ini
cd /software/oracle/admin/igt/utl_file 
rm TAP_FILE_REPORT_20151001_20151031.xls
bye 

DWHCMD.ini 

lcd D:\Report\files

cd /software/oracle/admin/igt/utl_file

mget TAP_FILE_REPORT_*.xls

bye

SFTPCMD2.bat
sftp -b DWHCMD.ini oracle@10.227.3.150

SFTPCMD3.bat
sftp -b DELDWHCMD.ini oracle@10.227.3.150