Need to run generate a series of INSERT INTO sql files, one SQL file per Version-Product combination.
The hierarchy is Main.bat => Version.bat => Product.bat => PROD_X_Generate_sql.bat => Generates sql file.
The generated file is zipped, and moved to output folder.
The files:
main_exttract.bat
handle_version.bat
handle_product.bat
main_extract.bat:
- Reading ini parameters file
- Calling handle_version.bat
handle_version.bat
- Set product parameters
- Call handle_product.bat
handle_product.bat
Call specific bat files, per product type.
Each product bat file
Is calling sqlplus, and generating INSERT INTO output file.
===========================
settings.ini
===========================
SOURCE_PATH=L:\code\Development\DB Utils\Extract\Code
OUTPUT_PATH=L:\code\Development\DB Utils\Extract\OutputLOGS_PATH=L:\code\Development\DB Utils\Extract\Logs
VERSIONS=Extract 3.1;Extract 3.2.0;Extract 3.2.[1-9];Extract 3.3;Extract 4.0 - By Service;Extract 4.1;
SOURCE_FOLDER_PROD_A=Extract PROD_A
SOURCE_FOLDER_PROD_B=Extract PROD_B
SOURCE_FOLDER_PROD_C=Extract PROD_C
OUTPUT_FOLDER_PROD_A=Extracted_PROD_A
OUTPUT_FOLDER_PROD_B=Extracted_PROD_B
OUTPUT_FOLDER_PROD_C=Extracted_PROD_C
ZIP_CMD=7zip
ZIP_CMD_PATH=C:\Program Files\7-Zip
RUN_DATE=SYSDATE
main_extract.bat:
===========================
ECHO OFF
setlocal
cls
ECHO =============================================
ECHO "main extract is starting"
ECHO =============================================
ECHO.
SET inifile=settings.ini
FOR /F "tokens=* delims==" %%i IN (%inifile%) DO (
FOR /F "tokens=1,2 delims==" %%a IN ("%%i") DO (
SET %%a=%%b
)
)
echo.
echo =============================================
echo ini file settings.ini was loaded:
echo =============================================
echo SOURCE_PATH=%SOURCE_PATH%
echo OUTPUT_PATH=%OUTPUT_PATH%
echo.
echo.
SET PATH=%PATH%;%ZIP_CMD_PATH%
echo.
FOR /F "tokens=1,2,3,4,5,6,7,8,9,10,11,12 delims=;" %%a IN ("%VERSIONS%") DO (
REM echo "%%a" "%%b" "%%c" "%%d" "%%e" "%%f" "%%g" "%%h" "%%i" "%%j" "%%k" "%%l"
REM set ver_array=%%a %%b %%c %%d %%e %%f %%g %%h %%i %%j %%k %%l
set ver_3_1=%%a
set ver_3_2_0=%%b
set ver_3_2_1=%%c
set ver_3_3=%%d
set ver_4_0=%%e
set ver_4_1=%%f
set future_version_x_1=%%g
set future_version_x_2=%%h
set future_version_x_3=%%i
set future_version_x_4=%%j
set future_version_x_5=%%k
set future_version_x_6=%%l
)
ECHO.
set run_year=%date:~-4%
set run_month=%date:~4,2%
set run_day=01
set EXTRACT_DATE=%RUN_DATE%
set hh=%time:~0,2%
set mm=%time:~3,2%
set ss=%time:~6,2%
set TIMESTAMP=%run_year%%run_month%%run_day%_%hh%%mm%%ss%
set LOG_FILE=%LOGS_PATH%\EXTRACT_%TIMESTAMP%.log
IF %RUN_DATE% == SYSDATE (
set EXTRACT_DATE=%run_year%%run_month%%run_day%
)
ECHO.
echo =============================================
echo System Date is: %DATE%
echo Running with date: %EXTRACT_DATE%
echo =============================================
ECHO.
set CURR_VER=%ver_3_1%
call handle_version.bat
set CURR_VER=%ver_3_2_0%
call handle_version.bat
set CURR_VER=%ver_3_2_1%
call handle_version.bat
set CURR_VER=%ver_3_3%
call handle_version.bat
set CURR_VER=%ver_4_0%
call handle_version.bat
set CURR_VER=%ver_4_1%
call handle_version.bat
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
===========================
handle_version.bat
===========================ECHO OFF
setlocal
ECHO.
ECHO =============================================
ECHO %CURR_VER% is Starting
ECHO =============================================
ECHO.
set step=build_InsertList.bat
set COMMON_WORK_AREA=common
ECHO.
ECHO =============================================
ECHO Calling %step%
ECHO =============================================
ECHO.
cd "%SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%"
call %step%
cd "%SOURCE_PATH%
ECHO.
ECHO =============================================
ECHO Step %step% Completed Successfully
ECHO =============================================
ECHO.
set WORK_AREA=%SOURCE_FOLDER_PROD_A%
set OUTPUT_FOLDER=%OUTPUT_FOLDER_PROD_A%
set PRODUCT_SCRIPT=EXTRACT_PROD_A.bat
set TARGET_FILE=LoadExtracted
call handle_product.bat
set WORK_AREA=%SOURCE_FOLDER_PROD_B%
set OUTPUT_FOLDER=%OUTPUT_FOLDER_PROD_B%
set PRODUCT_SCRIPT=EXTRACT_PROD_B.bat
set TARGET_FILE=LoadExtracted
call handle_product.bat
set OUTPUT_FOLDER=%OUTPUT_FOLDER_PROD_C%
set PRODUCT_SCRIPT=EXTRACT_PROD_C.bat
set TARGET_FILE=LoadExtracted
call handle_product.bat
ECHO =============================================
ECHO %CURR_VER% has finished
ECHO =============================================
ECHO.
===========================
handle_product.bat:
===========================
ECHO OFF
setlocalECHO.
ECHO =============================================
ECHO =============================================
ECHO %WORK_AREA% is Starting
ECHO =============================================
ECHO =============================================
ECHO.
cd "%SOURCE_PATH%\%CURR_VER%\%WORK_AREA%\"
set step=%PRODUCT_SCRIPT%
ECHO.
ECHO =============================================
ECHO Calling %step% in %WORK_AREA%
ECHO =============================================
ECHO.
CALL %step%
ECHO.
ECHO =============================================
ECHO Step %step% Completed Successfully
ECHO =============================================
ECHO.
set step="%TARGET_FILE%.zip creation"
ECHO.
ECHO =============================================
ECHO Start %step% Step
ECHO =============================================
ECHO.
IF EXIST "%SOURCE_PATH%\%CURR_VER%\%WORK_AREA%\%TARGET_FILE%.sql" (
move /Y "%SOURCE_PATH%\%CURR_VER%\%WORK_AREA%\%TARGET_FILE%.sql" "%SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%\"
cd %SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%
CALL zipper.bat
del /Q "%SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%\%TARGET_FILE%.sql"
)
ECHO.
ECHO =============================================
ECHO Step %step% Completed Successfully
ECHO =============================================
set step="Move %TARGET_FILE%.zip to %OUTPUT_PATH%\%CURR_VER%\%OUTPUT_FOLDER%\%EXTRACT_DATE% Folder"
ECHO.
ECHO =============================================
ECHO Start %step% Step
ECHO =============================================
ECHO.
IF EXIST "%SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%\%TARGET_FILE%.zip" (
IF NOT EXIST "%OUTPUT_PATH%\%CURR_VER%\%OUTPUT_FOLDER%\%EXTRACT_DATE%" (
md "%OUTPUT_PATH%\%CURR_VER%\%OUTPUT_FOLDER%\%EXTRACT_DATE%"
)
move /Y "%SOURCE_PATH%\%CURR_VER%\%COMMON_WORK_AREA%\%TARGET_FILE%.zip" "%OUTPUT_PATH%\%CURR_VER%\%OUTPUT_FOLDER%\%EXTRACT_DATE%"
)
ECHO.
ECHO =============================================
ECHO Step %step% Completed Successfully
ECHO =============================================
ECHO.
ECHO.
ECHO =============================================
ECHO =============================================
ECHO %WORK_AREA% has finished
ECHO =============================================
ECHO =============================================
ECHO.
===========================
zipper.bat
===========================
IF EXIST LoadExtractedRI.sql (
IF "%ZIP_CMD%" == "7zip" (7z a -y -tzip LoadExtractedRI.zip LoadExtractedRI.sql -p1234 | FIND "Creating archive")
)
===========================
set_user.sql
===========================
define user=my_user
define pass=my_user
define connectstr=my_connection_str
===========================
build_InsertSPList.bat
===========================
sqlplus -s /nolog @build_InsertSPList.sql
===========================
build_InsertSPList.sql
===========================
sqlplus -s /nolog @build_InsertList.sql
@set_user.sql
SET TERMOUT OFF
Conn &&user/&&pass@&&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 InsertList.sql
SPOOL InsertList.sql
PROMPT @set_user.sql
PROMPT SET TERMOUT OFF
PROMPT
PROMPT conn &&user/&&pass@&&connectstr
PROMPT SET SHOW OFF
PROMPT SET HEAD OFF
PROMPT SET LINE 1000
PROMPT SET FEEDBACK OFF
PROMPT SET PAGES 0
PROMPT
SELECT 'DELETE FROM EXTRACT_LIST;' FROM DUAL;
PROMPT
-- EXTRACT_LIST
SELECT
'INSERT INTO EXTRACT_LIST (provider_id) VALUES('||EXT_LIST.provider_id||');'
FROM
PROVIDERS
WHERE 1=1
ORDER BY EXT_LIST.provider_id;
SELECT 'COMMIT;' FROM DUAL;
PROMPT EXIT
PROMPT /
SPOOL OFF
EXIT
/
===========================
extract_product.bat
===========================
ECHO.
ECHO Running common/InsertList.sql
sqlplus -s /nolog @../common/InsertList.sql
ECHO.
ECHO Running common/PROD_Extract.sql 24
sqlplus -s /nolog @../common/PROD_Extract.sql 24
===========================
PROD_Extract.sql
===========================
@../common/set_user.sql
SET TERMOUT OFF
Conn &&user/&&pass@&&connectstr
SET SHOW OFF VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 100 TRIMS ON
SPOOL LoadExtracted.sql
-- File Header
SELECT 'CREATION DATE:'||TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI')||chr(13)||chr(10) ||
'ITEM ID:&&1'||chr(13)||chr(10) ||
'VERSION:310' ||chr(13)||chr(10) ||
'VERSION ID:'||AUTO_UPDATE_SEQ.NEXTVAL FROM DUAL;
PROMPT SET DEFINE OFF
-- TEMP_REGIONS
SELECT 'INSERT INTO TEMP_REGIONS(REGION_ID, REGION_NAME, TS_LAST_MODIFIED) '||
'VALUES ('|| REGION_ID || ','''|| SUBSTR(REPLACE(REGION_NAME, '''',''''''),1,50) || ''', SYSDATE); '
FROM GEOGRAPHIC_REGIONS;
-- TEMP_OPERATOR_GROUPS
SELECT 'INSERT INTO TEMP_OPERATOR_GROUPS(OPERATOR_GROUP_ID, OPERATOR_GROUP_NAME, TS_LAST_MODIFIED) '||
'VALUES ('|| GROUP_ID || ','''|| SUBSTR(REPLACE(GROUP_NAME, '''', ''''''),1,50) || ''', SYSDATE); '
PROMPT COMMIT
PROMPT /
SPOOL OFF
EXIT
===========================
PRODUCT_A.bat
===========================
ECHO.
ECHO Running common/InsertList.sql
sqlplus -s /nolog @../common/InsertList.sql
ECHO.
ECHO Running common/Extract.sql 24
sqlplus -s /nolog @../common/Extract.sql 24
===========================
PRODUCT_B.bat
===========================
ECHO.
ECHO Running common/InsertList.sql
sqlplus -s /nolog @../common/InsertList.sql
ECHO.
ECHO Running common/Extract.sql 32
sqlplus -s /nolog @../common/Extract.sql 32
No comments:
Post a Comment