Apex 上传文件至Windows网盘
1.前期工作
1. 服务端挂载网盘
安装
cifs-utils- yum install cifs-utils
参考文档
挂载网盘
1
2
3
4
5
6
7
8
9
10
11
12
13
14<!--查看oracle本地uid,gid等-->
[oracle@xxxxxx u01]$ id
uid=1002(oracle) gid=1002(oinstall) groups=1002(oinstall),1003(dba)
context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
<!--挂载目录-->
[root@xxxxxx ~]# mount -t cifs //xx.xx.xx.xxx/xxxxx -o uid=1002,gid=1002,rw,domain=xxxxxx,username=xxxxxx,nolock /u01/Upload/share_data
Password for xxxxxx@//xx.xx.xx.xxx/xxxxx: ********************
<!--验证目录权限-->
[root@xxxxxx ~]# ll /u01/Upload/share_data
总用量 45749
-rwxr-xr-x. 1 oracle oinstall 573174 6月 16 11:02 2019-2020-原表.xlsx
<!--可选卸载 挂载-->
[root@xxxxxx ~]# umount /u01/Upload/share_data
2. 在oracle中添加目录
1 |
|
3. 创建对应包并使用
PL/SQL包
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141create or replace PACKAGE UploadFilePackage as
-- Doc https://docs.oracle.com/en/database/oracle/oracle-database/20/arpls/UTL_FILE.html
-- 从apex_application_temp_files 中取BOLB数据 写入文件夹中
-- P_Key(外建id) + '_' + 文件名做唯一主键
procedure WriteBlobToUploadFile(P_Fileitem IN VARCHAR2, P_Appid IN VARCHAR2,
P_DirObject IN VARCHAR2, P_Key IN VARCHAR2);
-- 根据文件名(P_Key(外建id) + '_' + 文件名) 读文件并 提供下载请求
-- 如:
/*
begin
UploadFilePackage.ReadUploadFileToDownload(:P460_ID_FILENAME, :GLOBAL_PAYMENT_DIR);
end;
*/
procedure ReadUploadFileToDownload(P_Filename in VARCHAR2, P_DirObject in VARCHAR2,
P_Mimetype in varchar2 default 'text/plain');
-- 根据文件名(P_Key(外建id) + '_' + 文件名) 删除文件
-- 如:
/*
declare
V_FileName varchar2(100);
V_Payment_Directory varchar2(2000) := :GLOBAL_PAYMENT_DIR;
V_Outsourcing_Directory varchar2(2000) := :GLOBAL_OUTSOURCING_DIR;
V_Procurement_Directory varchar2(2000) := :GLOBAL_PROCUREMENT_DIR;
V_Maintenance_Directory varchar2(2000) := :GLOBAL_MAINTENANCE_DIR;
begin
-- 删除表中记录
select PDF_FILENAME into V_FileName from XXXXXX where id = :P13_ID;
DELETE XXXXXX where id = :P13_ID;
if :P13_PAYMENT_ID is not null then
-- 删除网盘文件
UploadFilePackage.DeleteUploadFile(
P_Filename => V_FileName,
P_DirObject => V_Payment_Directory);
end if;
commit;
end;
*/
procedure DeleteUploadFile(P_Filename in VARCHAR2, P_DirObject in VARCHAR2);
end UploadFilePackage;
create or replace PACKAGE BODY UploadFilePackage as
PROCEDURE WriteBlobToUploadFile(P_Fileitem IN VARCHAR2, P_Appid IN VARCHAR2,
P_DirObject IN VARCHAR2, P_Key IN VARCHAR2)
IS
v_blob BLOB;
v_filename varchar2(2000);
v_buffer RAW(32767);
blob_length INTEGER;
out_file UTL_FILE.FILE_TYPE;
chunk_size BINARY_INTEGER := 32767;
blob_position INTEGER := 1;
vErrMsg VARCHAR2(2000);
BEGIN
-- substr('171686777406224703//trusted.xml', instr('171686777406224703/trusted.xml', '/')+1,length('171686777406224703/trusted.xml'))
v_filename := substr(P_Fileitem, instr(P_Fileitem, '/') + 1, length(P_Fileitem));
v_filename := P_Key || '_' || v_filename;
apex_debug.message('--------UploadFileNmae-------');
apex_debug.message(v_filename);
-- Retrieve the BLOB for reading
BEGIN
SELECT blob_content
INTO v_blob
FROM apex_application_temp_files
WHERE name = P_Fileitem
and application_id = P_Appid;
EXCEPTION
WHEN OTHERS THEN
vErrMsg := 'No data found';
END;
-- 检索BLOB的大小
blob_length := DBMS_LOB.GETLENGTH(v_blob);
-- 打开要写入blob的位置的句柄
-- 注意:“wb”参数表示“以字节模式写入”，并且只有可在UTL_FILE pkg中使用Oracle 10g或更高版本。
-- 使用'w'代替pre Oracle 10q数据库。
out_file := UTL_FILE.FOPEN(P_DirObject, v_filename, 'wb', chunk_size);
-- 将BLOB以块的形式写入文件
WHILE blob_position <= blob_length
LOOP
IF ((blob_position + chunk_size - 1) > blob_length) THEN
chunk_size := blob_length - blob_position + 1;
END IF;
dbms_lob.read(v_blob, chunk_size, blob_position, v_buffer);
UTL_FILE.put_raw(out_file, v_buffer, TRUE);
blob_position := blob_position + chunk_size;
END LOOP;
UTL_FILE.FCLOSE(out_file);
delete apex_application_temp_files WHERE name = P_Fileitem and application_id = P_Appid;
EXCEPTION
WHEN OTHERS THEN
apex_debug.warn('-----WriteBlobToUploadFile: Error----------');
IF UTL_FILE.IS_OPEN(out_file) THEN
UTL_FILE.FCLOSE(out_file);
END IF;
END WriteBlobToUploadFile;
Procedure ReadUploadFileToDownload(P_Filename in VARCHAR2, P_DirObject in VARCHAR2,
P_Mimetype in varchar2 default 'text/plain')
IS
v_lob BLOB;
v_BFile BFILE;
v_src_offset NUMBER := 1;
v_dest_offset NUMBER := 1;
BEGIN
dbms_lob.createtemporary(v_lob, FALSE, DBMS_LOB.SESSION);
v_BFile := BFileName(P_DirObject, P_Filename);
dbms_lob.fileOpen(v_BFile);
dbms_lob.loadblobfromfile(dest_lob => v_lob,
src_bfile => v_BFile,
amount => dbms_lob.getLength(v_BFile),
dest_offset => v_dest_offset,
src_offset => v_src_offset);
dbms_lob.fileClose(v_BFile);
--download file
htp.init;
-- https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types
owa_util.mime_header(P_Mimetype, false);
htp.p('Content-length: ' || sys.dbms_lob.getlength(v_lob));
htp.p('Content-Disposition: attachment; filename="' || P_Filename || '"');
htp.p('Cache-Control: max-age=3600'); -- 浏览器缓存一个小时，根据需要进行调整
owa_util.http_header_close;
wpg_docload.download_file(v_lob);
apex_application.stop_apex_engine;
End ReadUploadFileToDownload;
procedure DeleteUploadFile(P_Filename in VARCHAR2, P_DirObject in VARCHAR2)
is
begin
UTL_FILE.FREMOVE(P_DirObject, P_Filename);
end DeleteUploadFile;
end UploadFilePackage;下载地址
1
2
3
4
5
6
7
8
9
10
11
select PAYMENT_ID,
listagg(concat(concat(concat(concat('<a href="',
APEX_PAGE.GET_URL(p_page=>:APP_PAGE_ID,
p_items=>'P460_ID_FILENAME',
p_values=>PDF_FILENAME,
p_request=>'DownloadUploadFile'))
, '">'), PDF_FILENAME), '</a>'), '<br>')
within group (order by PAYMENT_ID) as DownloadRrl
from xxxxxx
group by PAYMENT_ID
