Batch and PL/SQL by example. Get DB connections, execute anonymous PL/SQL block on each of these connections.

Description.
This is an example of running batch and PL/SQL anonymous block code.

The flow:
1. Populate db_list.ini file with connection_string list.
2. main.bat is reading ini file, and for each entry is calling run_sql.bat
3. run_sql.bat is wrapping the call to SQL. 
    It is handling sqlplus connection, and logging, via spool.
4.  handle_logic.sql - Handle Business Logic. 
     For purpose of example, the actual logic is not important. 
     In this case, the code is called in case stored procedure execution has caused a lock.
     The code is attempting to kill the offending session, and depending on the results, either throws an Exception, or calls again the stored procedure.

The code

db_list.ini
conn_str_A
conn_str_B


main.bat
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
SET REFRESH_LOG=find_lock.txt
SET TEMP_REFRESH_LOG=temp_find_lock.txt

ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program Attempts to resolve lock in DB entry from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_LOG%
ECHO. 2>%MAIN_LOG%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO handling %%i
    call run_sql.bat %TEMP_LOG% %%i
    TYPE %TEMP_LOG% >> %MAIN_LOG%
ECHO.
)

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

run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO business_logic.sql %db_name%
sqlplus -s system/system_pass@%db_name% @business_logic.sql %spool_to_file% %db_name%

business_logic.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
SET SERVEROUTPUT ON

SPOOL &&1

DECLARE

  CURSOR get_kill_job_sql_cur IS
  SELECT 'ALTER SYSTEM KILL SESSION '''||v_session.sid||','||v_session.serial#||'''IMMEDIATE' str_cmd
  FROM   v$session v_session,
         v$process v_process,    
         DBA_JOBS_RUNNING,
 DBA_JOBS
  WHERE  1 = 1
    AND  DBA_JOBS.what = 'MY_PACKAGE.my_proc;'
    AND  DBA_JOBS.job = DBA_JOBS_RUNNING.job
    AND  v_session.sid = DBA_JOBS_RUNNING.sid
    AND  v_session.type != 'BACKGROUND'
    AND  v_process.addr = v_session.paddr;
  
  v_invalid_state_exep  EXCEPTION; 
  v_failed_execute_exep EXCEPTION; 
  v_failed_refresh_exep EXCEPTION; 

  v_sql_str VARCHAR2(4000);  
  v_step VARCHAR2(100);  
  v_seperator VARCHAR2(100) := '=============================================';

  
BEGIN
  DECLARE
    v_counter NUMBER := 0;
  BEGIN
    v_step := 'Generate ALTER SYSTEM KILL SESSION IMMEDIATE sql statement';
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Starting............. '||v_step||CHR (10));
     FOR element IN get_kill_job_sql_cur LOOP
      v_counter := v_counter + 1;
      v_sql_str := element.str_cmd;
    END LOOP;

    IF v_counter <> 1 THEN
      RAISE v_invalid_state_exep;
    END IF;
  
    DBMS_OUTPUT.PUT_LINE('Step Finished Successfully'||CHR (10));
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed to Execute Step:.........'||v_step||CHR (10));
DBMS_OUTPUT.PUT_LINE('Need to handle manually'||CHR (10));
DBMS_OUTPUT.PUT_LINE('Error Details: '||SUBSTR(SQLERRM, 1, 1000));
RAISE v_invalid_state_exep;
  END;

  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
    v_step := 'Execute ALTER SYSTEM KILL SESSION IMMEDIATE sql statement';
    DBMS_OUTPUT.PUT_LINE('Starting............. '||v_step||CHR (10));
    DBMS_OUTPUT.PUT_LINE('about to Run SQL: ............ '||v_sql_str||CHR (10));
    EXECUTE IMMEDIATE v_sql_str;
    DBMS_OUTPUT.PUT_LINE('Step Finished Successfully'||CHR (10));
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
  EXCEPTION
    WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to Execute Step:.........'||v_step||CHR (10));
DBMS_OUTPUT.PUT_LINE('Need to handle manually'||CHR (10));
DBMS_OUTPUT.PUT_LINE('Error Details: '||SUBSTR(SQLERRM, 1, 1000));

RAISE v_failed_execute_exep;

  END;

  BEGIN
    v_step := 'Refresh Process';
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Starting............. '||v_step||CHR (10));
    APP_USER.MY_PACKAGE.start_my_proc;
    DBMS_OUTPUT.PUT_LINE('Step Finished Successfully'||CHR (10));
    DBMS_OUTPUT.PUT_LINE(v_seperator||CHR (10));
  EXCEPTION
    WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to Execute Step:.........'||v_step||CHR (10));
DBMS_OUTPUT.PUT_LINE('Need to handle manually'||CHR (10));
DBMS_OUTPUT.PUT_LINE('Error Details: '||SUBSTR(SQLERRM, 1, 1000));
RAISE v_failed_execute_my_proc_exep;
END;
EXCEPTION
  WHEN v_invalid_state_exep THEN
    DBMS_OUTPUT.PUT_LINE('Error During refresh_lock !!!'||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Error in Step: '||v_step||CHR (10));
  WHEN v_failed_execute_exep THEN
    DBMS_OUTPUT.PUT_LINE('Error During refresh_lock !!!'||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Error in Step: '||v_step||CHR (10));
  
  WHEN v_failed_refresh_exep THEN
    DBMS_OUTPUT.PUT_LINE('Error During refresh_lock !!!'||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Error in Step: '||v_step||CHR (10));
  
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error During refresh_lock !!!'||CHR (10));
    DBMS_OUTPUT.PUT_LINE('Unexpected Error!!!'||CHR (10));
END;
/
EXIT;
/