All posts by admin

Batch common to create folder using month year name

@echo off

for /f “tokens=1-4 delims=/ ” %%i in (“%date%”) do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%

setlocal EnableDelayedExpansion
set months[“01”]=January
set months[“02”]=February
set months[“03”]=March
set months[“04”]=April
set months[“05”]=May
set months[“06”]=June
set months[“07”]=July
set months[“08”]=August
set months[“09”]=September
set months[“10”]=October
set months[“11”]=November
set months[“12”]=December

SET dirname=\\host\common\folder name with spaces\!months[“%month%”]!%year%
echo “%dirname%” >>d:\dts\log.txt
mkdir “%dirname%”
move D:\dts\incoming\* “%dirname%”
echo “exit code %errorlevel%” >>d:\dts\log.txt

Bat command run etl task

Rem How to run
Rem the 3rd arg for the bat will be the data file name pattern
Rem the 2nd args for the bat is the DATE

@ECHO off
SET “configfile=C:\Batch\scripts\dbconn.daz”
SET “logfile=C:\Batch\Logs\%1%_%2%.log”
SET “ClientPath=C:\Oracle\product\11.2.0\client_1\BIN”

IF NOT EXIST %configfile% GOTO ExitLogInError

REM IF NOT EXIST %datafile% GOTO ExitError

SET /p connstr= <%configfile%

ECHO @C:\Batch\scripts\etl_task.sql %1 %2 %3 | %ClientPath%\sqlplus %connstr% > “%logfile%”
REM findstr /c:”FAILURE” “%logfile%” >nul 2>&1 && echo “run time failure” || echo “run success”
findstr /c:”(FAILURE)” “%logfile%” >nul 2>&1 && GOTO¬† Exit_failure || GOTO Exit_Success
:Exit_Success

SET ERRORLEVEL=0
ECHO “Success!
GOTO Exit_last

:Exit_failure

SET ERRORLEVEL=3
ECHO “Failure”
Exit 3

:ExitLogInError
ECHO config File %configfile% does not exist
EXIT 1

:Exit_last

ECHO “Exit code is %ERRORLEVEL% for this run”

—-

–set term off echo off heading off verify off
–exec user.PKG_Trans_Archiving.ArchiveTransactions(‘&1’,to_date(‘&2′,’yymmdd’));
set serveroutput on;
EXEC ETL_RUN_TASK.run_task(‘&1′,’&2′,’&3’);
exit;

Bat command running db task (script)

Rem How to run
Rem the 3rd arg for the bat will be the data file name pattern
Rem the 2nd args for the bat is the DATE

@ECHO off
SET “configfile=C:\Batch\scripts\dbconnx.daz”
SET “ClientPath=C:\Oracle\product\11.2.0\client_1\BIN”

IF NOT EXIST %configfile% GOTO ExitLogInError

REM IF NOT EXIST %datafile% GOTO ExitError

SET /p connstr= <%configfile%
%ClientPath%\sqlplus %connstr% @C:\Batch\scripts\Db_task.sql

IF %ERRORLEVEL% gtr 0 GOTO Exit_failure

:Exit_Success

SET ERRORLEVEL=0
ECHO “Success!
GOTO Exit_last

:Exit_failure

SET ERRORLEVEL=3
ECHO “Failure”
Exit 3

:ExitLogInError
ECHO config File %configfile% does not exist
EXIT 1

:Exit_last

ECHO “Exit code is %ERRORLEVEL% for this run”

—-

set serveroutput on;
EXEC SCHEMA_NAME.PKG_NAME.SP_NAME;
exit;