PL/SQ and sqlplus batch by example. Delete from Table in Three Steps

=======================
General
=======================
Generic delete from  a table using Business Logic.
Several million of records are being deleted.
The flow is a three steps process:

Step1.

pre_run_steps.sh

This create backup table for SOURCE_DATA_SUBSCRIBER (SOURCE_DATA_SUBSCRIBER_BAK) 

Creates additional temporary tables which are to be used during actual delete step.


Step2.

main_delete.sh 

The actual DELETE. 

Create the PL/SQL Package, which handles the business logic.

Call the PL/SQL package.

Progress is logged into SGA_W_LOG Table.

Step3.

post_run_steps.sh
This drop the SOURCE_DATA_SUBSCRIBER_BAK and drop additional temporary tables created in pre_run_steps.sh

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

Step 0.
set_vipuser.sql

Step1.

pre_run_steps.sh

pre_run_steps.batpre_run_steps.sql


Step2.

main_delete.shmain_delete.bat

main_delete.sql

MULTI_DELETE.sql

Step3.

post_run_steps.shpost_run_steps.batpost_run_steps.sql

Step 0.
set_vipuser.sql
--Set the connection detailsdefine vipuser=my_userdefine vippass=my_passdefine connectStr=MY_INSTANCE

Step 1.

pre_run_steps.sh
sqlplus /nolog @pre_run_steps.sql

pre_run_steps.bat

sqlplus /nolog @pre_run_steps.sql

pre_run_steps.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2;

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SOURCE_DATA_SUBSCRIBER_BAK'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SOURCE_DATA_SUBSCRIBER_BAK';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

PROMPT INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

COMMIT;

PROMPT CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SGA_LOC_GRP_OUTBACK_NETWORKS'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS (network_id        NUMBER(10) ) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE TABLE   MSISDN_PROFILE_NOT_PRE

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'MSISDN_PROFILE_NOT_PRE'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   MSISDN_PROFILE_NOT_PRE (msisdn VARCHAR2(30) NOT NULL) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE MSISDN_PROFILE_NOT_PRE';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

SELECT DISTINCT network_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUP_ENTRIES 

   WHERE location_group_id IN 

         (SELECT location_group_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUPS 

WHERE LOCATION_GROUP_NAME='Outback');

commit;

PROMPT INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

SELECT KEY1 FROM SOURCE_USER.SFI_CUSTOMER_PROFILE WHERE ATTR1 NOT IN ('RogEngPre','RogFrPre','FidoEngPre','FidoFrPre');

commit;

PROMPT CREATE INDEX SFI_CST_PROF_IX

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_INDEXES WHERE index_name = 'SFI_CST_PROF_IX'; 

  IF v_counter > 0 THEN

    v_sql_str := 'DROP INDEX SFI_CST_PROF_IX';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

BEGIN

  DBMS_STATS.gather_table_stats('&&vipuser','MSISDN_PROFILE_NOT_PRE');

END;

/

PROMPT Finished Pre Run step

EXIT;

Step 2.

main_delete.sh

sqlplus /nolog @main_delete.sql

main_delete.bat

sqlplus /nolog @main_delete.sql

main_delete.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE PROCEDURE MULTI_DELETE

@MULTI_DELETE.sql

PROMPT EXECUTE PROCEDURE MULTI_DELETE

PROMPT .............................

PROMPT Running DELETE....

PROMPT Follow Up on process Progress by: SELECT * FROM SGA_W_LOG ORDER BY TS_LAST_MODIFIED;

PROMPT .............................

BEGIN

  MULTI_DELETE;

END;

/

PROMPT Finished Run step  

EXIT;

MULTI_DELETE.sql

CREATE OR REPLACE PROCEDURE MULTI_DELETE IS

    v_effected_rows NUMBER;

    v_delete_rows NUMBER;

    v_row_counter NUMBER;

    v_table_name VARCHAR2(30);

    v_sql_str VARCHAR2(1000);

    v_module_name VARCHAR2(30);

    v_msg_text VARCHAR2(1000);

  BEGIN

    v_table_name :='SOURCE_DATA_SUBSCRIBER';

    v_effected_rows := 1;

    v_row_counter := 0;

    v_module_name := 'MULTI DELETE';

    v_sql_str := 'DELETE FROM SOURCE_DATA_SUBSCRIBER WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS ) AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE) AND ROWNUM < 100001';

    v_msg_text  := 'Running SQL: '||v_sql_str;

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    v_msg_text  := 'Starting at: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss');

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    WHILE v_effected_rows > 0 LOOP

      DELETE FROM SOURCE_DATA_SUBSCRIBER

       WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS )

         AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE)

         AND ROWNUM < 100001;

      v_effected_rows := SQL%ROWCOUNT;

      v_row_counter := v_row_counter + v_effected_rows;

      COMMIT;

      

      v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;      

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

      

    END LOOP;

    

    COMMIT;

    

    v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

  END MULTI_DELETE;

/

Step 3.

post_run_steps.shsqlplus /nolog @post_run_steps.sql

post_run_steps.bat
sqlplus /nolog @post_run_steps.sql

post_run_steps.sql

@../set_vipuser.sqlSET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK
DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK;

PROMPT DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS
DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS;

PROMPT DROP TABLE MSISDN_PROFILE_NOT_PRE
DROP TABLE MSISDN_PROFILE_NOT_PRE;

PROMPT Finished Post Run step
EXIT;

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;

Code example for batch, SQLoader, sqlplus

==========================
General
==========================
The main idea is to get data from a table, from several Databases, and load the data into a single table.

1. main_capacity_loader.bat 
- Set run date in format YYYYMMDD_hh24mmss
- Sets environment variables
- Calls main_extact_capacity.bat

2. main_extact_capacity.bat:- Calls extract_capacity.bat
- Moves generated file to input directory for SQL Loader
- Calls sql_loader_main.bat
- Moves processed file to output directory for SQL Loader

3. extract_capacity.bat
- Reads ini file, with DB connections details
- Per each connections calls run_sql.bat

4. run_sql.bat
- Wrapper to get_capacity_counters.sql

5. get_capacity_counters.sql
- Calls Handles the actual access to remote DB.

6.  sql_loader_main.bat
- Recieved output file from extract_capacity.bat. The fields are comma delimited, and loads, via SQL LOADER into Database.

==========================
main_capacity_loader.bat
==========================
ECHO OFF
setlocal
cls

ECHO.
ECHO =============================================
ECHO main is starting
ECHO =============================================

ECHO.
ECHO =============================================
ECHO set run date
ECHO =============================================

set run_year=%date:~-4%
set run_month=%date:~4,2%
set run_day=%date:~7,2%
set hh=%time:~0,2%
set mm=%time:~3,2%
set ss=%time:~6,2%

IF %run_month% LSS 10 (set run_month=0%run_month:~1,1%)
IF %run_day% LSS 10 (set run_day=0%run_day:~1,1%)
IF %hh% LSS 10 (set hh=0%hh:~1,1%)
IF %mm% LSS 10 (set mm=0%mm:~1,1%)
IF %ss% LSS 10 (set ss=0%ss:~1,1%)

set RUN_DATE=%run_year%%run_month%%run_day%_%hh%%mm%%ss%
ECHO Start Run at %RUN_DATE%

ECHO.
ECHO =============================================
ECHO init parameters
ECHO =============================================

SET HOME_DIR=d:\RDG\Capacity\CAPACILY_LOADER
SET LOG_FILE=%HOME_DIR%\Logs\capacity_load_%RUN_DATE%.log
SET INIFILE=%HOME_DIR%\db_list.ini
SET SPOOL_TO_FILE=%HOME_DIR%\capacity_counters.dat

ECHO LOG_FILE: %LOG_FILE%

ECHO.
ECHO. > %LOG_FILE%
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE% >> %LOG_FILE%
     CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO.
ECHO =============================================
ECHO.


ECHO ============================================= >> %LOG_FILE%
ECHO main_capacity_loader.bat has finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%

REM EXIT

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

main_extact_capacity.bat

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

ECHO OFF

setlocal

cls

ECHO.

ECHO =============================================

ECHO main is starting

ECHO =============================================

ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%

SET HOME_DIR=%1

SET RUN_DATE=%2

SET LOG_FILE=%3

SET INIFILE=%4

SET SPOOL_TO_FILE=%5

ECHO Inside main_extact_capacity.bat >> %LOG_FILE%

ECHO.

ECHO =============================================

ECHO init parameters

ECHO =============================================

SET SQL_LOADER_DIR=%HOME_DIR%\SQL_LOADER

SET SQL_LOADER_DIR_HANDLED=%SQL_LOADER_DIR%\HANDLED

SET SQL_LOADER_MAIN=%SQL_LOADER_DIR%\sql_loader_main.bat

SET SQL_LOADER_FILE=%SQL_LOADER_DIR%\input_capacity.dat

SET SQL_LOADER_FILE_HANDLED=%SQL_LOADER_DIR_HANDLED%\input_capacity_%RUN_DATE%.dat

ECHO.

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO Delete old capacity_counters.dat file

ECHO Delete old capacity_counters.dat file >> %LOG_FILE%

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO DEL %SPOOL_TO_FILE% 

ECHO DEL %SPOOL_TO_FILE%  >> %LOG_FILE%

DEL %SPOOL_TO_FILE%

ECHO.

ECHO. >> %LOG_FILE%

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

ECHO CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE% >> %LOG_FILE%

     CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

ECHO.

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO.

ECHO ============================================= >> %LOG_FILE%

ECHO Move capacity_counters.dat file to SQL_LOADER >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO =============================================

ECHO Move capacity_counters.dat file to SQL_LOADER

ECHO =============================================

ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE% >> %LOG_FILE%

ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%

     MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%

ECHO.

ECHO ============================================= >> %LOG_FILE%

ECHO Running SQL_LOADER >> %LOG_FILE%

ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO =============================================

ECHO Running SQL_LOADER

ECHO =============================================

ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% 

     CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% 

 

ECHO =============================================

ECHO %SQL_LOADER_MAIN% has Finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO %SQL_LOADER_MAIN% has Finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

 

ECHO.

ECHO =============================================

ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%

ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED% >> %LOG_FILE%

     MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%

 

ECHO.

ECHO =============================================

ECHO main_extact_capacity.bat has finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO main_extact_capacity.bat has finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

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

extract_capacity.bat

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

ECHO OFF

setlocal

cls

ECHO.

ECHO =============================================

REM CALL extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

SET HOME_DIR=%1

SET SPOOL_TO_FILE=%2

SET INIFILE=%3

SET LOG_FILE=%4

SET SQL_FILE=%HOME_DIR%\code\get_capacity_counters.sql

ECHO ============================================= >> %LOG_FILE%

ECHO Inside extract_capacity.bat >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO =============================================

ECHO extract_capacity.bat is starting

ECHO This Program read entries from file %inifile%

ECHO =============================================

ECHO.

FOR /F "tokens=*" %%i IN (%inifile%) DO (

ECHO --------------------------------------------------------

ECHO Handling %%i 

 ECHO --------------------------------------------------------

ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO Handling %%i  >> %LOG_FILE%

ECHO -------------------------------------------------------- >> %LOG_FILE%

call :process_one_db %%i %LOG_FILE% %SQL_FILE% %SPOOL_TO_FILE% %HOME_DIR%

)

ECHO.

ECHO =============================================

ECHO extract_capacity has finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO extract_capacity has finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

GOTO :EOF

:process_one_db

  SET CONNECTION_NAME=%1

  SET LOG_FILE=%2

  SET SQL_FILE=%3

  SET SPOOL_TO_FILE=%4

  SET HOME_DIR=%5

  

  REM ECHO CONNECTION_NAME %CONNECTION_NAME%

  SET IS_REMARK=%CONNECTION_NAME:~0,1%

  IF [%IS_REMARK%]==[#] (

    ECHO Skip Processing %CONNECTION_NAME% 

ECHO Skip Processing %CONNECTION_NAME%  >> %LOG_FILE%

  ) ELSE (

    ECHO call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE%  >> %LOG_FILE%

    call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE%    

ECHO --------------------------------------------------------

ECHO Done

        ECHO --------------------------------------------------------

ECHO.

ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO Done >> %LOG_FILE%

        ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO. >> %LOG_FILE%

  )

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

run_sql.bat

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

SET db_name=%1

SET sql_file_name=%2

SET spool_to_file=%3

REM ECHO db_name: %db_name%

REM ECHO sql_file_name: %sql_file_name%

REM ECHO spool_to_file: %spool_to_file%

REM ECHO sqlplus %db_name% @%sql_file_name% %spool_to_file% 

     sqlplus %db_name% @%sql_file_name% %spool_to_file% 

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

get_capacity_counters.sql

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

SET SHOW OFF 

SET VERIFY OFF 

SET HEAD OFF

SET LINE 500

SET FEEDBACK OFF

SET PAGES 0

SET TRIMS ON

SET TERMOUT OFF  

SET COLSEP ,

SET LINESIZE 140

--SPOOL capacity_counters.dat APPEND;

SPOOL &&1 APPEND;

SELECT TRIM(USER),

       SUBSTR(TO_CHAR(static_id),1,30) AS static_id,

       TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss') AS RUN_DATE,

       ROUND(SUM(CNT_VALUE)/900) AS value   

  FROM SUPPORT_CNT_LOG, DUAL 

 WHERE VALUE_DATE >= ADD_MONTHS(SYSDATE,-6) 

   AND TO_CHAR(static_id) NOT LIKE '9999999%' 

   AND TO_CHAR(static_id) NOT LIKE '1011500104190000' 

   AND CNT_VALUE IS NOT NULL 

GROUP BY TO_CHAR(static_id), TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss');

EXIT;

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

db_list.ini

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

# Use # to mark out lines

#-------------------------------------------------------

USER_A/PASS_A@CONN_STR_A

USER_B/PASS_B@CONN_STR_B

USER_C/PASS_C@CONN_STR_C

USER_D/PASS_D@CONN_STR_D

#USER_E/PASS_E@CONN_STR_E

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

sql_loader_main.bat
==========================
ECHO OFF
REM sqlldr collector/ncgwcol@igt control=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_ct_aps1.dat log=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_aps1_log.log

SET SQL_LOADER_DIR=%1
REM SET SQL_LOADER_DIR=D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER

sqlldr CAPACITY/CAPACITY@CGW_SA control=%SQL_LOADER_DIR%\loader_capacity.ctl log=%SQL_LOADER_DIR%\loader_capacity.log

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

loader_capacity.ctl

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

LOAD DATA

INFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\input_capacity.dat'

BADFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\bad_capacity.bad'

discardmax 0

TRUNCATE

into table CAPACITY_COUNTER_DATA

fields terminated by ','

(

 schema_name    "TRIM(:schema_name)",

 counter_id     "TRIM(:counter_id)",

 run_date       "TRIM(:run_date)",

 counter_value  "TRIM(:counter_value)"

)