获取文件列表
往服务器上写文件参照如下代码
declare
file_handle UTL_FILE.FILE_TYPE;
begin
file_handle := utl_file.fopen('TMP','log.txt','W',20000);
utl_file.put_line(file_handle,'测试日志');
utl_file.fclose(file_handle);
end;
-- 1). 创建临时表
create global temporary table dirlist(
filename varchar2(255),
filesize number,
filedate date)
on commit delete rows;
-- 2). 创建JAVA SOURCE过程
create or replace and compile java source named DirList as
import java.io.*;
import java.sql.*;
public class DirList
{
public static void getList(String directory) throws SQLException
{
File path=new File(directory);
String[] fileList=path.list();
String fileName;
long fileSize;
long fileDate;
for (int i=0;i0 THEN
nm_forms_flag:=1;
clos_NUM :=0;
--打开游标
OPEN cur_clos(TEXT_BUFFER);
LOOP
FETCH cur_clos INTO key_clos;
EXIT WHEN cur_clos%NOTFOUND;
clos_num:=clos_num+1;
IF clos_num=1 THEN
vc_model:=key_clos.column_value;
ELSIF clos_num=2 THEN
vc_minchange:=key_clos.column_value;
ELSIF clos_num=3 THEN
vc_lotno:=key_clos.column_value;
ELSIF clos_num=4 THEN
vc_lotqty:=key_clos.column_value;
ELSIF clos_num=5 THEN
vc_receivedate:=key_clos.column_value;
ELSIF clos_num=6 THEN
vc_freezeflg:=key_clos.column_value;
END IF;
END LOOP;
--判断字段的合法性
IF vc_model IS NULL OR CheckLength(vc_model,1,17)=0 OR
CheckAlphaNum(vc_model)=0 THEN
nm_forms_flag:=0;
ELSIF vc_minchange IS NULL OR CheckLength(vc_minchange,1,2)=0 OR CheckAlphaNum(vc_minchange)=0 THEN
nm_forms_flag:=0;
ELSIF vc_lotno IS NULL OR CheckLength(vc_lotno,1,2)=0 OR CheckAlphaNum(vc_lotno)=0 THEN
nm_forms_flag:=0;
ELSIF CheckNum(vc_lotqty)=0 THEN
nm_forms_flag:=0;
ELSIF vc_receivedate IS NULL OR CheckDate(vc_receivedate)=0 THEN
nm_forms_flag:=0;
ELSIF Checklength(vc_freezeflg,1,1)=0 OR
CheckAlphaNum(vc_freezeflg)=0 THEN
nm_forms_flag:=0;
END IF;
CLOSE cur_clos;
IF nm_forms_flag=1 THEN
--打开游标
OPEN key_kdpartstock(vc_model,vc_minchange,vc_lotno);
LOOP
FETCH key_kdpartstock INTO cur_key;
EXIT WHEN key_kdpartstock%NOTFOUND;
key_no := 1;
END LOOP;
--KD在库情
报存在,做修改操作
IF key_no=1 THEN
UPDATE DP_M_KDPARTSTOCK
SET
freezeflg=vc_freezeflg
WHERE model=vc_model AND
minchange =vc_minchange AND
lotno=vc_lotno;
ELSE
--添
加数据
INSERT INTO DP_M_KDPARTSTOCK(
model,
minchange,
lotno,
lotqty,
receivedate,
freezeflg
)VALUES(
vc_model,
vc_minchange,
vc_lotno,
vc_lotqty,
to_date(vc_receivedate,'YYYY-MM-DD'),
vc_freezeflg
);
commit;
END IF;
END IF;
CLOSE
key_kdpartstock;
END
IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
exit;
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
DBMS_OUTPUT.PUT_LINE('INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('INVALID FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('READ ERROR');
WHEN
UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('WRITE ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INTERNAL ERROR');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
if
nm_forms_flag=0 then
--
增加一条记录
Create_Record;
:BLOCK_RESULT.TEXT_HANDLE_INFO := 'KD文件格式不存在';
rollback;
end if ;
END LOOP;
END IF;
END LOOP;
IF nm_kd_flag=0 THEN
--增加一条记录
Create_Record;
:BLOCK_RESULT.TEXT_HANDLE_INFO := 'KD文件不存在';
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER ERROR='||SQLERRM); END;
--
-- IF nm_changeFlag=1 or nm_changeFlag=2 THEN -- --判断文件是否存在
-- IF vc_kdName is null THEN
-- nm_kd_flag:=0;
-- --增加一条记录
-- Create_Record;
-- :BLOCK_RESULT.TEXT_HANDLE_INFO := 'KD文件不存在';
-- ELSE
-- nm_kd_flag:=1;
--
-- --判断格式
-- END IF;
-- END IF;
--
-- IF nm_changeFlag=1 or nm_changeFlag=3 THEN
-- IF vc_plName is null THEN -- nm_pl_flag:=0;
-- --增加一条记录
-- Create_Record; -- :BLOCK_RESULT.TEXT_HANDLE_INFO := 'pl文件不存在';
-- ELSE
-- nm_pl_flag:=1; -- END IF;
-- END IF;
--
--
--
--
-- IF nm_pl_flag=1 THEN -- nm_forms_flag:=INSTR(vc_plName,',',1,7);
-- IF nm_forms_flag=0 THEN -- nm_pl_flag:=0;
-- --增加一条记录
-- Create_Record; -- :BLOCK_RESULT.TEXT_HANDLE_INFO := 'pl文件格
式不对';
-- ELSE
-- IF vc_model1 IS NULL OR
CheckLength(vc_model1,1,17)=0 OR CheckAlphaNum(vc_model1)=0 THEN
-- nm_pl_flag:=0;
-- ELSIF vc_minchange IS NULL OR
CheckLength(vc_minchange,1,2)=0 OR
CheckAlphaNum(vc_minchange)=0 THEN
-- nm_pl_flag:=0;
-- ELSIF vc_blno IS NULL OR
CheckLength(vc_blno,1,4)=0 OR CheckAlphaNum(vc_blno)=0
THEN
-- nm_pl_flag:=0;
-- ELSIF vc_lotno1 IS NULL OR
CheckLength(vc_lotno1,1,5)=0 OR CheckAlphaNum(vc_lotno1)=0 THEN
-- nm_pl_flag:=0;
-- ELSIF vc_caseno IS NULL OR
CheckLength(vc_caseno,1,3)=0 OR CheckAlphaNum(vc_caseno)=0 THEN
-- nm_pl_flag:=0;
-- ELSIF vc_partsno IS NULL OR
CheckLength(vc_partsno,1,3)=0 OR CheckAlphaNum(vc_partsno)=0 THEN
-- nm_pl_flag:=0;
-- ELSIF vc_qty IS NULL OR
CheckLength(vc_qty,1,3)=0 OR CheckNum(vc_qty)=0 THEN -- nm_pl_flag:=0;
-- END IF;
-- END IF;
-- END IF;
--
-- IF nm_pl_flag=1 THEN
--
--
--
--EXCEPTION
-- WHEN OTHERS THEN
-- nm_err_code := SQLCODE;
-- vc_err_msg := substr(SQLERRM(nm_err_code), 1, 200); -- ROLLBACK TO SAVEPOINT savepoint_insert; -- nm_ret_o := ShowStatus('BLOCK_STATUSBAR', 'TEXT_STATUSBAR', :global.gch_username, :BLOCK_CONSTS.TEXT_PGID, 'P010CMI114', :BLOCK_CONSTS.TEXT_PGID); -- RETURN;
--END;