Batch and sqlplus by example. Call bat file, that would execute sqlplus

The Flow
Need to run a SQL on remote database(s), and report the results.
In this case, the SQL logic is split into several steps.
As system, connect to remote DB and kill any stuck session.
As schema owner, connect to remote DB, and refresh snaspshot.

The files:
bat files
main_main.bat                   - Main bat file, that is calling all other bat files.
main_delete.bat                 - Call delete_refresh_job.sql
main_kill_sessions.bat     - Call gen_kill_sessions.sql
main_refresh_1by1.bat     - Call refresh_All_1by1.sql
main_create.bat                 - Call create_refresh_job.sql
main_run_refresh.bat      - Call run_refresh_job.sql

sql files
delete_refresh_job.sql     - Delete "stuck" job. (The "stuck" job is refresh of a group)  
gen_kill_sessions.sql       - Kill "stuck" job session
refresh_All_1by1.sql        - Refresh Group snapshots one by one
create_refresh_job.sql    - Create refresh job
run_refresh_job.sql         - Run refresh job 

parameter files
db_list.ini           - The remote database we need to connect to.
db_system.ini   - System account on remote database.

Code on Remote DB
The logic is in package on the Remote DB
REFRESH_PKG

===========================
Code example
===========================

===========================
parameter files
===========================

db_list.ini
===========================
userA/passA@connect_str

db_system.ini
===========================
system/pass@connect_str

===========================
bat files
===========================

===========================
main_main.bat        
===========================

ECHO OFF
setlocal
cls

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_delete.bat
call main_delete.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_kill_sessions.bat
call main_kill_sessions.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_refresh_1by1.bat
call main_refresh_1by1.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_create.bat
call main_create.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_run_refresh.bat
call main_run_refresh.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
main_delete.bat      
===========================

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=delete_result.txt
SET TEMP_REFRESH_LOG=delete.log

ECHO.
ECHO =============================================
ECHO main_delete.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=delete_refresh_job.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================



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

main_kill_sessions.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_system.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=kill_sessions.log
SET TEMP_REFRESH_LOG=temp_kill_sessions.log

ECHO.
ECHO =============================================
ECHO main_kill_sessions.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=gen_kill_sessions.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

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

main_refresh_1by1.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=refresh.log
SET TEMP_REFRESH_LOG=temp_refresh.log


ECHO.
ECHO =============================================
ECHO main_refresh_1by1.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%
SET SQL_FILE=IG1_Refresh_All_1by1.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


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

main_create.bat 

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET PROCESS_LOG=create.log
SET TEMP_PROCESS_LOG=temp_create.log

ECHO.
ECHO =============================================
ECHO main_create.bat is starting
ECHO This Program read entries from file %inifile% and report results to %PROCESS_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_PROCESS_LOG%
ECHO. 2>%PROCESS_LOG%

SET SQL_FILE=create_refresh_job.sql
ECHO Running %SQL_FILE%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_PROCESS_LOG% %%i %SQL_FILE%
    TYPE %TEMP_PROCESS_LOG% >> %PROCESS_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

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

main_run_refresh.bat 

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

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=run_refresh_result.log
SET TEMP_REFRESH_LOG=run_refresh.log

ECHO.
ECHO =============================================
ECHO main_run_refresh.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=run_refresh_job.sql
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
sql files
===========================


===========================
delete_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  SH_REFRESH_PKG.delete_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
gen_kill_sessions.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  

EXEC DBMS_LOCK.sleep(20);
  
@kill_sessions.sql   

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  
EXEC DBMS_LOCK.sleep(20);  

@kill_sessions.sql   

EXIT

/

===========================
refresh_All_1by1.sql
===========================
SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_SS');                            
EXEC DBMS_SNAPSHOT.refresh('ALARM_SS','F');                                   
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_FILE_CONFIG_SS');                  
EXEC DBMS_SNAPSHOT.refresh('ALARM_FILE_CONFIG_SS','F');                         
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_MAP_SS');                     
EXEC DBMS_SNAPSHOT.refresh('ALARM_MAP_SS','F');                            
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_NAME_SS');                         
EXEC DBMS_SNAPSHOT.refresh('ALARM_NAME_SS','F');                                
                                                                     
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALLOCATOR_SS');                          
EXEC DBMS_SNAPSHOT.refresh('ALLOCATOR_SS','F');                                 
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: BILLING_SERVICE_SS');                    
EXEC DBMS_SNAPSHOT.refresh('BILLING_SERVICE_SS','F');                           
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: WORLDWIDE_LIST_SS');                     
EXEC DBMS_SNAPSHOT.refresh('WORLDWIDE_LIST_SS','F');                            

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Completed');
EXEC DBMS_OUTPUT.PUT_LINE('----------------------');

EXIT

/

===========================
create_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  REFRESH_PKG.create_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
run_refresh_job.sql 
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK ON
SET PAGES 500
SET TRIMS ON

SPOOL &&1

EXEC DBMS_OUTPUT.PUT_LINE('Starting REFRESH_PKG.refresh_prc;');    

begin
  -- Call the procedure
  REFRESH_PKG.refresh_prc;
end;
/

EXEC DBMS_OUTPUT.PUT_LINE('Finished REFRESH_PKG.refresh_prc;');    

SPOOL OFF
EXIT

/

===========================
REFRESH_PKG
===========================

CREATE OR REPLACE PACKAGE BODY REFRESH_PKG IS

===========================
REFRESH_PRC
===========================

PROCEDURE REFRESH_PRC IS

g_name  varchar2(30);

BEGIN

    

    -- Create the current entry in the Master log table)

    SELECT DISTINCT SUBSTR(process_name,1,12)

    into g_name 

    FROM process_information

    WHERE process_name like '%REFRESH%';

    INSERT INTO REFRESH_LOG@MASTER_DB(DB_NAME,LAST_REFRESH_START) 

    VALUES (g_name,SYSDATE);

    COMMIT;

    

    DBMS_REFRESH.REFRESH('MASTER_GROUP');

    

    UPDATE REFRESH_LOG@MASTER_DB SET LAST_REFRESH_END=SYSDATE 

    WHERE DB_NAME=g_name 

      AND LAST_REFRESH_START=(SELECT MAX(LAST_REFRESH_START) 

                                FROM REFRESH_LOG@MASTER_DB7 

                               WHERE DB_NAME=g_name);

    COMMIT;

     

END REFRESH_PRC;

===========================
CREATE_REFRESH_JOB
===========================

PROCEDURE CREATE_REFRESH_JOB IS

  X NUMBER;  

BEGIN

    DELETE_REFRESH_JOB;

    

    SYS.DBMS_JOB.SUBMIT

    ( job       => X 

     ,what      => 'REFRESH_PKG.REFRESH_PRC;'

     ,next_date => trunc(sysdate+1)+1/24+10/1440

     ,interval  => 'trunc(sysdate+1) +1/24+10/1440'

     ,no_parse  => TRUE );

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=1

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

END CREATE_REFRESH_JOB;

===========================
DELETE_REFRESH_JOB
===========================

PROCEDURE DELETE_REFRESH_JOB IS

cursor job_remove is

    select job,what from user_jobs where 

    what like '%REFRESH_PRC%';

BEGIN

    for  rec in job_remove loop

        dbms_job.remove(rec.job);

    end loop;

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=0

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

    

END DELETE_REFRESH_JOB;

END REFRESH_PKG;