获取文件列表
Íù?þÎñÆ?ÉÏÐ?ÎÄ?þ?ÎÕÕÈçÏÂ?úÂë
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;