Batch script by example. Run batch, that call sqlplus that generate csv files, batch file merge these files into one.

The Task
Need to generate csv file, which holds data collected from several schemas in same Instance.
The output file should hold data about services and ports used by applications. Each schema represents a different application.


The Flow
In order to query several schemas, the script creates new user, "query_user".
This user is granted SELECT ANY TABLE and CREATE VIEW permissions.
For each schema, a new view is created, queried, and then dropped.
The output of the query, is saved to csv file.

The flow in general:
Run main batch file.
Batch file calls main sqlplus script.
Open loop on schemas.
 - The sqlplus script builds a series of sqlplus scripts, each one per specific task.
 - The sqlplus script then executes the generated scripts.
 - The data is retrieved via view owned by "query_user" on another schema at a time.
 - Each generated script generates its own csv file.
Close loop on schemas.
Main sqlplus script terminates.
Batch file calls another batch script, that merges the generated csv files into one file.
Cleanup script, drop query_user, drop view, etc.

The files:
bat files
main_get_ports.bat
main_retrieve_data.bat
merge_output_files.bat
cleanup_output_files.bat

sqlplus files
main_get_ports.sql
set_connection.sql

create_query_user.sql

build_grant_permissions.sql
grant_permissions_user.sql
grant_permissions.sql

build_create_view_and_select.sql
create_view_and_select.sql
create_view.sql

select_header_procs.sql
select_header_params.sql
select_from_view_procs.sql
select_from_view_params.sql

drop_view.sql

===========================
main_get_ports.bat
===========================

ECHO OFF
SET HOME_PATH=L:\Functionality\PORTS
SET INPUT_FOLDER=L:\Functionality\PORTS\scripts
SET OUTPUT_FOLDER=L:\Functionality\PORTS
SET OUTPUT_FILE_NAME=ports_list.csv

ECHO.
ECHO -------------------------------------
ECHO Starting Running Script
ECHO -------------------------------------
ECHO.
ECHO Generating script: %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%
ECHO.

cd %HOME_PATH%
cd scripts
call main_retrieve_data.bat
cd %HOME_PATH%
call scripts\merge_output_files.bat %HOME_PATH% %INPUT_FOLDER% %OUTPUT_FOLDER% %OUTPUT_FILE_NAME%
call scripts\cleanup_output_files.bat %HOME_PATH%\scripts

ECHO -------------------------------------
ECHO Completed Successfully
ECHO -------------------------------------

===========================
main_retrieve_data.bat
===========================
sqlplus -s /nolog @main_get_ports.sql

===========================
main_get_ports.sql
===========================

@set_connection.sql
--SET TERMOUT OFF

Conn &&query_user/&&query_pass@&&query_connectstr

SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMSPOOL ON


PROMPT -----------------------;
PROMPT Grant Permissions Start
@build_grant_permissions.sql
@grant_permissions.sql
SET TERMOUT ON
PROMPT Grant Permissions Completed
PROMPT -----------------------;
PROMPT

PROMPT
PROMPT -----------------------;
PROMPT build_create_view_and_select.sql Start
@build_create_view_and_select.sql
SET TERMOUT ON
PROMPT build_create_view_and_select.sql Completed
PROMPT -----------------------;
PROMPT


PROMPT
PROMPT -----------------------;
PROMPT create_view_and_select.sql Start
@create_view_and_select.sql
SET TERMOUT ON
PROMPT create_view_and_select.sql Completed
PROMPT -----------------------;
PROMPT
EXIT;
/

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

create_query_user.sql

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

------------------------------------------------
--Login as system/sysdba to run this script
------------------------------------------------
CREATE USER query_user IDENTIFIED BY query_user;
GRANT CONNECT TO query_user;
GRANT CREATE VIEW TO query_user;
GRANT SELECT ANY TABLE TO query_user;

EXIT;

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

set_connection.sql

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

DEFINE query_user=query_user
DEFINE query_pass=query_user
DEFINE query_connectstr=inst_test
DEFINE main_user=query_user 

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

build_grant_permissions.sql

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

@set_connection.sql
SET TERMOUT OFF

Conn &&query_user/&&query_pass@&&query_connectstr
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMS ON

-- This goes into grant_permissions.sql
SPOOL grant_permissions.sql
PROMPT -------------------------------;
PROMPT SET TERMOUT OFF
PROMPT SET SHOW OFF
PROMPT SET HEAD ON
PROMPT SET FEEDBACK OFF
PROMPT SET VERIFY OFF

--PROMPT DEFINE main_user='';
--PROMPT COLUMN main_user new_value main_user for a30;
--PROMPT SELECT SYS_CONTEXT('USERENV','SESSION_USER') main_user FROM DUAL;
--PROMPT SELECT '&&main_user' from dual;
PROMPT -------------------------------;
PROMPT

SELECT '@grant_permissions_user.sql'||' '||owner||' '||owner||' '||'&&query_connectstr' || ' '||'&&main_user' FROM ALL_TABLES WHERE TABLE_NAME = 'PROCESS';
SPOOL OFF
/

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

grant_permissions.sql

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

This file is generated by build_grant_permissions.sql
It looks like this:

-------------------------------
SET TERMOUT OFF
SET SHOW OFF
SET HEAD ON
SET FEEDBACK OFF
SET VERIFY OFF
-------------------------------

@grant_permissions_user.sql SCHEMA_A_USER SCHEMA_A_PASS CONNECT_STR query_user

@grant_permissions_user.sql SCHEMA_B_USER SCHEMA_B_PASS CONNECT_STR query_user

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

grant_permissions_user.sql

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

SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET TRIMS ON

DEFINE user_name=&&1
DEFINE user_pass=&&2
DEFINE conn_str=&&3
DEFINE main_user=&&4

conn &&user_name/&&user_pass@&&conn_str

GRANT SELECT ON PRODUCT TO &&main_user;
GRANT SELECT ON CONFIGURATION_PROPERTIES TO &&main_user;
GRANT SELECT ON PROCESS TO &&main_user;
/

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

build_create_view_and_select.sql

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

@set_connection.sql
--SET TERMOUT OFF

Conn &&query_user/&&query_pass@&&query_connectstr

SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMSPOOL ON

SPOOL create_view_and_select.sql
PROMPT -------------------------------;
--PROMPT PROMPT inside SPOOL create_view_and_select.sql
SET TERMOUT OFF
SET SHOW OFF
SET VERIFY OFF
SET HEADING ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMSPOOL ON

PROMPT -------------------------------;
PROMPT

--PROMPT SPOOL ports_list.csv
--SELECT '@create_view.sql'||' '||owner||';'||CHR(10)||'@select_from_view.sql'||' '||'ports_list_'||owner||'.csv'||';' ||CHR(10)||'SET HEADING OFF'||CHR(10) FROM ALL_TABLES WHERE TABLE_NAME = 'PROCESS';

SELECT '@create_view.sql'||' '||owner||';'FROM ALL_TABLES WHERE TABLE_NAME = 'PROCESS';
PROMPT SET HEADING ON
SELECT '@select_header_procs.sql'||' '||'ports_proc_header.csv'||';' FROM ALL_TABLES WHERE ROWNUM < 2;
SELECT '@select_header_params.sql'||' '||'ports_params_header.csv'||';' FROM ALL_TABLES WHERE ROWNUM < 2;
PROMPT SET HEADING OFF
SELECT '@create_view.sql'||' '||owner||';'||CHR(10)||'@select_from_view_procs.sql'||' '||'ports_proc_list_'||owner||'.csv'||';' FROM ALL_TABLES WHERE TABLE_NAME = 'PROCESS';
SELECT '@create_view.sql'||' '||owner||';'||CHR(10)||'@select_from_view_params.sql'||' '||'ports_params_list_'||owner||'.csv'||';' FROM ALL_TABLES WHERE TABLE_NAME = 'PROCESS';
SELECT '@drop_view.sql'||';' FROM DUAL;
--PROMPT SPOOL OFF
PROMPT /
SPOOL OFF
/

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

create_view_and_select.sql

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

This file is generated by build_create_view_and_select.sql

It looks like this:

@create_view.sql SCHEMA_A_USER;
@create_view.sql SCHEMA_B_USER;
SET HEADING ON
@select_header_procs.sql ports_proc_header.csv;
@select_header_params.sql ports_params_header.csv;
SET HEADING OFF
@create_view.sql SCHEMA_A_USER;
@select_from_view_procs.sql ports_proc_list_RUS_TEST1_SPARX.csv;

@create_view.sql SCHEMA_B_USER;
@select_from_view_procs.sql ports_proc_list_RUS_TEST2_SPARX.csv;

@create_view.sql SCHEMA_A_USER;
@select_from_view_params.sql ports_params_list_SCHEMA_A_USER.csv;

@create_view.sql SCHEMA_B_USER;
@select_from_view_params.sql ports_params_list_SCHEMA_B_USER.csv;

@drop_view.sql;
/

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

Handle Logic files

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

Following files handle the logic.

They are not important for a general example.

create_view.sql

select_header_procs.sql

select_header_params.sql

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

create_view.sql

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

DEFINE my_user=&&1;

--SET HEAD ON

--PROMPT --------------------------------------------;

--PROMPT Running create_view.sql for user '&&my_user'

--PROMPT --------------------------------------------;

CREATE OR REPLACE VIEW PROCESS_PORTS_VW AS

SELECT RTRIM('&&my_user') AS schema_name,

       RTRIM('PRODUCTS CONFIGURATION') AS source,

       item_id AS service_name,

       PRODUCTS.jvm_id AS jvm_id,

       PRODUCTS.jmx_port AS jmx_port,      

       CONFIGURATION.property_value AS property_value,

       CONFIGURATION.property_description AS property_description,

       NULL AS process_name,

       NULL AS process_type,      

       NULL AS process_port,

       NULL AS server_port

      FROM &&my_user..PRODUCTS PRODUCTS,

           &&my_user..CONFIGURATION_PROPERTIES CONFIGURATION    

 WHERE PRODUCTS.product_id = CONFIGURATION.property_id

   AND 1=1

UNION ALL

SELECT '&&my_user' AS schema_name,

       'PROCESS' AS source,

       NULL AS service_name,

       NULL AS jvm_id,      

       NULL AS jmx_port,

       NULL AS property_value,

       NULL AS property_description,

       process_name,

       process_type,

       process_port,

       server_port      

FROM   &&my_user..PROCESS

 WHERE 1=1

ORDER BY process_name   ;

--PROMPT --------------------------------------------;

--PROMPT create_view.sql Completed

--PROMPT --------------------------------------------;

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

select_header_procs.sql

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

DEFINE my_file=&&1;

SET COLSEP ,    

SET TERMOUT OFF

SET SHOW OFF

SET VERIFY OFF

SET LINESIZE 1000

SET FEEDBACK OFF

SET PAGES 10

SET TRIMSPOOL ON

SET UNDERLINE OFF

SET HEADING ON

SET NEWPAGE NONE

COL property_value FOR A50

COL service_name FOR A12

COL jvm_id FOR 999999

COL property_description FOR A150

SPOOL &&my_file

SELECT schema_name,

       source,

  process_name,

  process_type,

  process_port,

  server_port  

FROM PROCESS_PORTS_VW

WHERE source = 'PROCESS'

  AND ROWNUM < 2;

SPOOL OFF;

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

select_header_params.sql

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

DEFINE my_file=&&1;

SET COLSEP ,    

SET TERMOUT OFF

SET SHOW OFF

SET VERIFY OFF

SET LINESIZE 1000

SET FEEDBACK OFF

SET PAGES 10

SET TRIMSPOOL ON

SET UNDERLINE OFF

SET HEADING ON

SET NEWPAGE NONE

COL property_value FOR A50

COL service_name FOR A12

COL jvm_id FOR 999999

COL property_description FOR A150

SPOOL &&my_file

SELECT schema_name,

       source,

       service_name,

       jvm_id,

       jmx_port,

       property_value,

       property_description

FROM PROCESS_PORTS_VW

WHERE source = 'PRODUCTS CONFIGURATION'

  AND ROWNUM<2;

SPOOL OFF;

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

Generate csv file

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

Following file use the view, to retrieve data, and create csv files.

select_from_view_procs.sql
select_from_view_params.sql

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

select_from_view_procs.sql

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

DEFINE my_file=&&1;

SET COLSEP ,     
SET TERMOUT OFF
SET SHOW OFF
SET VERIFY OFF 
SET LINESIZE 1000
SET FEEDBACK OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET UNDERLINE OFF
SET HEADING OFF

COL property_value FOR A50
COL service_name FOR A12
COL jvm_id FOR 999999
COL property_description FOR A150

SPOOL &&my_file
SELECT schema_name,
       source,
  process_name,
  process_type,
  process_port,
  server_port   
FROM PROCESS_PORTS_VW
WHERE source = 'PROCESS';

SPOOL OFF;

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

select_from_view_params.sql

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

DEFINE my_file=&&1;

SET COLSEP ,     

SET TERMOUT OFF

SET SHOW OFF

SET VERIFY OFF 

SET LINESIZE 1000

SET FEEDBACK OFF

SET PAGES 0

SET TRIMSPOOL ON

SET UNDERLINE OFF

SET HEADING OFF

COL property_value FOR A50

COL service_name FOR A12

COL jvm_id FOR 999999

COL property_description FOR A150

SPOOL &&my_file

SELECT schema_name,

       source,

  service_name,

  jvm_id,

  jmx_port,

  property_value,

  property_description

FROM PROCESS_PORTS_VW

WHERE source = 'PRODUCTS CONFIGURATION';  

SPOOL OFF;

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

drop_view.sql

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

--PROMPT --------------------------------------------;

--PROMPT Drop PROCESS_PORTS_VW

--PROMPT --------------------------------------------;

DROP VIEW PROCESS_PORTS_VW;

--PROMPT --------------------------------------------;

--PROMPT Drop PROCESS_PORTS_VW Completed

--PROMPT --------------------------------------------;

===========================
merge_output_files.bat
===========================

ECHO OFF

SET HOME_PATH=%1%

SET INPUT_FOLDER=%2%

SET OUTPUT_FOLDER=%3%

SET OUTPUT_FILE_NAME=%4%

REM ---------------------------------------------------------

REM This program would merge all files into one file.

REM Target File: OUTPUT_FILE_NAME

REM Source files: 

REM Header Files:  ports_params_header.csv

REM                ports_proc_header.csv

REM Data Files:    ports_params_list_*.csv

REM                ports_proc_list_*.csv

REM ---------------------------------------------------------

TYPE %INPUT_FOLDER%\ports_params_header.csv | find "SCHEMA_NAME" > %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%

FOR %%i IN (%INPUT_FOLDER%\ports_params_list_*.csv) DO TYPE %%i >> %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%

ECHO. >> %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%

TYPE %INPUT_FOLDER%\ports_proc_header.csv | find "SCHEMA_NAME" >> %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%

FOR %%i IN (%INPUT_FOLDER%\ports_proc_list_*.csv) DO TYPE %%i  >> %OUTPUT_FOLDER%\%OUTPUT_FILE_NAME%

===========================
cleanup_output_files.bat
===========================
ECHO OFF
SET INPUT_FOLDER=%1%

REM ---------------------------------------------------------
REM This program would delete all temp files.
REM ---------------------------------------------------------

FOR %%i IN (%INPUT_FOLDER%\ports_params*.csv) DO DEL %%i

FOR %%i IN (%INPUT_FOLDER%\ports_proc*.csv) DO DEL %%i