|
-
create or replace and compile java source named util as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
create or replace function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleora92binimp.exe','execute');
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleora92binexp.exe','execute');
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleora92binsqlldr.exe','execute');
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleDBABIN*','execute');
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleDBABATCHBIN*','execute,read,write');
EXEC dbms_java.grant_permission('TEST','java.io.FilePermission','D:oracleDBABATCHSDAT*','read,write');
EXEC dbms_java.grant_permission('TEST','java.lang.RuntimePermission','*','writeFileDescripter') ;
/
|
|
- Win32 Shell
: _S_GET_TO_DO_LIST.bat
@echo off
c:windowssystem32cmd.exe /c "D:oracleDBABINfind2 D:oracleDBABATCHSDAT -name DS*.txt > d:oracleDBABATCHBINS_to_do.dat"
: _S_MAKE_CONTROL_FILE.bat
@echo off
c:windowssystem32cmd.exe /c "echo LOAD DATA > d:oracleDBABATCHBINub_rawdata_s_p.ctl"
c:windowssystem32cmd.exe /c "cat d:oracleDBABATCHBINS_to_do.dat|gawk "{print "INFILE '" $1"'"}" >> d:oracleDBABATCHBINub_rawdata_s_p.ctl"
c:windowssystem32cmd.exe /c "echo APPEND INTO TABLE UB_RAWDATA_S_P >> d:oracleDBABATCHBINub_rawdata_s_p.ctl"
c:windowssystem32cmd.exe /c "echo FIELDS TERMINATED BY ',' >> d:oracleDBABATCHBINub_rawdata_s_p.ctl"
c:windowssystem32cmd.exe /c "echo (MEASURE_DATE, SENSOR_CODE, MEASURE_TIME, MEASURE_VALUE) >> d:oracleDBABATCHBINub_rawdata_s_p.ctl"
:_S_LOADER_TO_RAW_S_P.bat
c:windowssystem32cmd.exe /c "d:oracleora92binsqlldr.exe ubiware/ubiware control=D:oracleDBABATCHBINub_rawdata_s_p.ctl log=D:oracleDBABATCHBINub_rawdata_s_p.log direct=true readsize=10245670"
: _S_DEL_COMPLETED_FILE.bat
c:windowssystem32cmd.exe /c "for /f %%Y in (D:oracleDBABATCHBINS_to_do.dat) do call d:oracleDBABINrm.exe -rf "%%Y""
- Procedure
CREATE OR REPLACE PROCEDURE PRC_LOAD_RAW_S
IS
n_todo_file_cnt NUMBER(5) ;
n_result1 NUMBER(1) ;
n_result2 NUMBER(1) ;
BEGIN
BEGIN
GET_DIR_LIST('D:oracleDBABATCHSDAT') ;
END ;
SELECT COUNT(*)
INTO n_todo_file_cnt
FROM DIR_LIST
WHERE FILENAME NOT LIKE 'LAST%' ;
-- D:oracleDBABATCHDAT 에 작업할 데이타화일이 있는지를 검사
IF n_todo_file_cnt > 0 THEN
SELECT RUN_CMD('D:oracleDBABATCHBIN_S_GET_TO_DO_LIST.bat')
INTO n_result1
FROM DUAL ;
IF n_result1 > 0 THEN
RETURN ;
END IF ;
-- CONTROLFILE 생성
BEGIN
RC('D:oracleDBABATCHBIN_S_MAKE_CONTROL_FILE.bat') ;
END ;
-- 정적 테이블에 LOADING
SELECT RUN_CMD('D:oracleDBABATCHBIN_S_LOADER_TO_RAW_S_P.bat')
INTO n_result2
FROM DUAL ;
IF n_result2 > 0 THEN
RETURN ;
END IF ;
-- 작업 완료된 화일 삭제
BEGIN
RC('D:oracleDBABATCHBIN_S_DEL_COMPLETED_FILE.bat') ;
END ;
END IF ;
COMMIT ;
RETURN ;
END ;
- 1분에 한번씩 실행하는 Job 생성
begin
sys.dbms_job.submit(job => :job,
what => 'PRC_LOAD_RAW_S ;',
next_date => to_date('06-04-2005 16:14:08', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/1440');
commit;
end;
/
|
Technote
