rem @echo off

for /F "tokens=2-4 delims=/ " %%i in ('date /t') do  set currentdate=%%k-%%i-%%j
for /F "tokens=1-3 delims=: " %%i in ('time /t') do set currenttime=%%i-%%j-%%k

set SET_CLEANUP_FILE_OLDER=60
set SET_PROMPT=n
set SET_SEMANTICS=n
set SET_RESTART=n
set _SCRIPT_DRIVE=%~d0
set APP_ROOT=%_SCRIPT_DRIVE%\ICOOP_ALL\ICORE

SET NLS_LANG=
rem SET SET NLS_LANG=THAI_THAILAND.TH8TISASCII
rem SET NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

SET ORACLE_HOME=
SET ORA_ADM_USR=
SET ORA_ADM_PWD=
SET ORA_ADM_EXP_USR=
SET ORA_ADM_EXP_PWD=
SET TEMP_DIR=%WINDIR%\TEMP

SET ORA_TARGET_DB_HOST=
SET ORA_TARGET_DB_PORT=
SET ORA_TARGET_DB_SID=
SET ORA_TARGET_DBSTR=%ORA_TARGET_DB_HOST%:%ORA_TARGET_DB_PORT%/%ORA_TARGET_DB_SID%
SET ORA_TARGET_USR=
SET ORA_TARGET_PWD=

SET ORA_VERSION=

SET DUMP_FILE=%ORA_TARGET_USR%
SET DATAPUMP=
SET DATAPUMP_DIR=
SET DATAPUMP_PATH=%DATAPUMP_DIR%:\%DATAPUMP%
SET DATAPUMP_ZIP_FILE=%DUMP_FILE%.%ORA_TARGET_DB_HOST%%ORA_TARGET_DB_SID%.%currentdate%_%currenttime%
SET DATAPUMP_ZIP_PATH=%APP_ROOT%\ICOOP\DB
SET BACKUP_PATH=
SET ZIPAPP=%APP_ROOT%\ICOOP\DB\7-ZipPortable\App\7-Zip64
SET PATH=%ORACLE_HOME%\bin;%PATH%;%windir%\system32\inetsrv;

SET ORA_OS_USR=
SET ORA_OS_PWD=
SET ORA_OS_DATAPUMP_PATH=\\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP%

appcmd set config /section:staticContent /+"[fileExtension='.log',mimeType='text/plain']"
appcmd set config /section:staticContent /+"[fileExtension='.bat',mimeType='text/plain']"

:STEP0
net use "\\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$" %ORA_OS_PWD% /user:%ORA_OS_USR%
mkdir \\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP%
echo y|CACLS \\%ORA_TARGET_DB_HOST%\%DATAPUMP_DIR%$\%DATAPUMP% /T /P Everyone:F
net use "%ORA_OS_DATAPUMP_PATH%" %ORA_OS_PWD% /user:%ORA_OS_USR%
mkdir %BACKUP_PATH%
echo y|CACLS %BACKUP_PATH% /T /P Everyone:F
rem mkdir %DATAPUMP_PATH%

echo quit|echo alter system set nls_length_semantics='CHAR' scope=both;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo CREATE OR REPLACE DIRECTORY %DATAPUMP% AS '%DATAPUMP_PATH%';|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant read, write directory on direcotry %DATAPUMP% to %ORA_ADM_EXP_USR%;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant read, write directory on direcotry %DATAPUMP% to %ORA_ADM_EXP_USR%;|sqlplus %ORA_TARGET_USR%/%ORA_TARGET_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo alter session set "_ORACLE_SCRIPT"=true;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo alter system set deferred_segment_creation = FALSE ;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant select on dba_jobs to sys;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant execute  on dbms_job to sys;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant select on dba_jobs to system;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
echo quit|echo grant execute  on dbms_job to system;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

IF "%SET_SEMANTICS%"=="n" GOTO :STEP2

:STEP1
echo quit|echo @Alter_Fix_Oracle_nls_length_semantics.sql;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

:STEP2
IF "%SET_RESTART%"=="n" GOTO :STEP4

:STEP3
cd %ORACLE_HOME%\bin
echo quit|echo shutdown immediate|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD% AS SYSDBA
echo quit|echo startup|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD% AS SYSDBA

:STEP4
rem echo quit|echo SELECT  'SYS.DBMS_IJOB.BROKEN('||job||',TRUE); END;' FROM DBA_JOBS_RUNNING;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
rem echo quit|echo SELECT  'BEGIN dbms_ijob.remove('||job||'); END;' FROM DBA_JOBS_RUNNING;|sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA

rem 
rem ========== Start =============
rem ใช้สำหรับกรณี Export เพิ่มไปนำเข้า Oracle XE 
rem sqlplus %ORA_ADM_USR%/%ORA_ADM_PWD%@%ORA_TARGET_DBSTR% AS SYSDBA
rem begin
 rem for i in (select ('ALTER TABLE '|| C.owner || '.' || C.table_name|| ' modify (' || C.column_name || ' '|| C.data_type || '(' || C.char_length|| ' CHAR))') as Q from all_tab_columns C, all_tables T where T.owner in ('ISCOICOOPTRN') and C.owner = T.owner and C.table_name = T.table_name and C.char_used = 'B' and C.table_name not in (select table_name from all_external_tables) and C.data_type in ('VARCHAR2', 'CHAR') ) LOOP
   rem BEGIN
	rem execute immediate i."Q"||' ';
   rem EXCEPTION 
        rem WHEN OTHERS 
        rem THEN dbms_output.put_line(SQLCODE||i."Q"||' ');
   rem END;	
 rem end loop;
rem end;
rem /
rem quit
rem ========== End =============

cd %TEMP_DIR%
del  /F /Q *.dmp

rem expdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% DUMPFILE=%DUMP_FILE%_METADATA_ONLY.DMP OWNER=%ORA_TARGET_USR% LOGFILE=%DUMP_FILE%_METADATA_ONLY.log SCHEMAS=%ORA_TARGET_USR% VERSION=%ORA_VERSION% CONTENT=METADATA_ONLY EXCLUDE=STATISTICS
rem expdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% DUMPFILE=%DUMP_FILE%_DATA_ONLY.DMP OWNER=%ORA_TARGET_USR% LOGFILE=%DUMP_FILE%_DATA_ONLY.log SCHEMAS=%ORA_TARGET_USR% VERSION=%ORA_VERSION% CONTENT=DATA_ONLY EXCLUDE=STATISTICS
rem expdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% DUMPFILE=%DUMP_FILE%.DMP OWNER=%ORA_TARGET_USR% LOGFILE=%DUMP_FILE%_EXPDP.log SCHEMAS=%ORA_TARGET_USR% VERSION=%ORA_VERSION% CONTENT=ALL EXCLUDE=STATISTICS

expdp %ORA_ADM_EXP_USR%/%ORA_ADM_EXP_PWD%@%ORA_TARGET_DBSTR% DIRECTORY=%DATAPUMP% DUMPFILE=%DUMP_FILE%.DMP OWNER=%ORA_TARGET_USR% LOGFILE=%DUMP_FILE%_EXPDP.log VERSION=%ORA_VERSION% SCHEMAS=%ORA_TARGET_USR% 

:STEP5
echo y|echo F|xcopy %ORA_OS_DATAPUMP_PATH%\%DUMP_FILE%.DMP %TEMP_DIR%\ /f /j /s /w /z /r /y
echo y|echo F|xcopy %ORA_OS_DATAPUMP_PATH%\%DUMP_FILE%_EXPDP.log %TEMP_DIR%\ /f /j /s /w /z /r /y
echo y|echo F|xcopy %ORA_OS_DATAPUMP_PATH%\%DUMP_FILE%_EXPDP.log %DATAPUMP_ZIP_PATH%\ /f /j /s /w /z /r /y
%ZIPAPP%\7z a %TEMP_DIR%\%DATAPUMP_ZIP_FILE%.zip %TEMP_DIR%\%DUMP_FILE%.DMP %TEMP_DIR%\%DUMP_FILE%_EXPDP.log 
copy %DATAPUMP_ZIP_FILE%.zip %DATAPUMP_ZIP_PATH%\%DATAPUMP_ZIP_FILE%.zip
copy %DUMP_FILE%_EXPDP.log %DATAPUMP_ZIP_PATH%\%DATAPUMP_ZIP_FILE%.log
copy %DATAPUMP_ZIP_FILE%.zip %BACKUP_PATH%\%DATAPUMP_ZIP_FILE%.zip
copy %DUMP_FILE%_EXPDP.log %BACKUP_PATH%\%DATAPUMP_ZIP_FILE%.log

rem del  /F /Q *.dmp
del  /F /Q *.zip
cd %DATAPUMP_ZIP_PATH%

forfiles /P %DATAPUMP_ZIP_PATH%\ /S /M *.zip /D -%SET_CLEANUP_FILE_OLDER% /C "cmd /c del @PATH"
forfiles /P %DATAPUMP_ZIP_PATH%\ /S /M *.log /D -%SET_CLEANUP_FILE_OLDER% /C "cmd /c del @PATH"

IF "%SET_PROMPT%"=="n" GOTO :STEP7
:STEP6
SET /P E=Pleasec enter to exit 

:STEP7