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
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 -------------------------------------
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;
/
--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;
--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
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
/
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
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;
/
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
/
--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;
/
@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_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
