Oracle APEX上传文件至Window网盘

[[Oracle APEX]] [[Oracle APEX19安装部署]]

场景

linux 下挂载公司网盘提示

1
2
3
4


[root@xxxxxxxxx ~]# mount -t cifs  //xxxxxxxxx/xxxxxxxxx/xxxxxxxxx  -o uid=2000,gid=2000,rw,domain=pharmatechs,username=xxxxxxxxx,nolock  /u01/xxxxxxxxx/xxxxxxxxx/xxxxxxxxx
Password for xxxxxxxxx@//xxxxxxxxx/xxxxxxxxx/xxxxxxxxx:  ***************              
mount error(22): Invalid argument
Refer to the mount.cifs(8) manual page (e.g. man mount.cifs) and kernel log messages (dmesg)  

1.前期工作

OsXM1-2024-08-16-10-22-21

1. Linux服务端挂载网盘

安装 cifs-utils支持 yum install cifs-utils

参考文档

1. Linux下Oracle不支持cifs?

2. Linux访问Windows共享文件夹，关于mount cifs的一个问题
挂载网盘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


<!--查看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

<!-- 查看挂载日志 -->
[root@xxxxxx ~]# dmesg |grep cifs
[299793.180079] Key type cifs.spnego registered
[299793.180085] Key type cifs.idmap registered
[299793.180534] cifs: Unknown parameter 'nolock'
[299830.016992] cifs: Unknown parameter 'nolock'
        

2. 在Oracle中添加目录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


-- system 用户
create or replace directory DFS_SHARE_DATA as '/u01/Upload/share_data';

-- 2、查询有哪些directory
select * from dba_directories;
-- 3、赋权
grant read,write on directory DFS_SHARE_DATA to user01;
-- 4、删除
drop directory DFS_SHARE_DATA;
-- 5、测试
DECLARE
    filehandle utl_file.file_type; --句柄
begin
    --打开文件
    filehandle := utl_file.fopen('DFS_SHARE_DATA', 'test-1.py', 'w'); 
    --写入一行记录
    utl_file.put_line(filehandle, '# -*- coding: utf-8 -*-');
    utl_file.put_line(filehandle, 'print("hi)');
    --关闭句柄
    utl_file.fclose(filehandle);
end;

2. 在Oracle APEX中创建对应的程序包.

获取 Oracle dbms_crypto 的授权grant execute on dbms_crypto to orcl;

  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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229


CREATE OR REPLACE PACKAGE UploadFilePackageV2 AS
    -- 依据上传的页项生成hash文件名和源文件名
    PROCEDURE GenerateHashFileName( P_FileItem IN VARCHAR2,
                                    O_HashFileName OUT varchar2,
                                    O_FileName OUT varchar2 );
    -- 将blob数据写成磁盘文件
    PROCEDURE WriteBlobToUploadFile( P_HashFileName IN VARCHAR2,
                                     P_FileBlob IN BLOB,
                                     P_DirObject IN VARCHAR2 );
    -- 读磁盘文件返回blob
    FUNCTION ReadUploadFileToBlob( P_HashFileName IN varchar2,
                                   P_DirObject IN VARCHAR2 ) RETURN blob;
    -- 依据blob 发起apex 下载附件
    PROCEDURE blobToDownload( P_FileName IN VARCHAR2,
                              p_blob IN blob,
                              P_MimeType IN VARCHAR2 DEFAULT 'text/plain' ) ;
    -- 从磁盘读取blob 并换气APEX下载
    PROCEDURE ReadUploadFileToDownload(
        P_FileName IN VARCHAR2,
        P_HashFileName IN varchar2,
        P_DirObject IN VARCHAR2,
        P_MimeType IN VARCHAR2 DEFAULT 'text/plain' );
    -- 从磁盘删除文件
    PROCEDURE DeleteUploadFile( P_HashFileName IN VARCHAR2,
                                P_DirObject IN VARCHAR2 );
END UploadFilePackageV2;
CREATE OR REPLACE PACKAGE BODY UploadFilePackageV2 AS
    PROCEDURE GenerateHashFileName( P_FileItem IN VARCHAR2,
                                    O_HashFileName OUT varchar2,
                                    O_FileName OUT varchar2 )
        IS
        ex_no_data_found_file_item EXCEPTION;
        v_file_type varchar2(200);
    BEGIN
        -- 检查是否为空
        IF P_Fileitem IS NULL THEN
            RAISE ex_no_data_found_file_item ;
        END IF;
        -- 获取上传的文件名
        O_FileName := SUBSTR(P_Fileitem, INSTR(P_Fileitem, '/') + 1, LENGTH(P_Fileitem));
        v_file_type := SUBSTR(O_FileName, INSTR(O_FileName, '.', -1));
        -- 根据上传的文件名 获取hash后的名字
        O_HashFileName := SYS.DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(O_FileName,
                                                                      'AL32UTF8'),
                                               DBMS_CRYPTO.HASH_SH1) || SYS_GUID() || v_file_type;
        --         O_HashFileName := SYS_GUID();
    EXCEPTION
        WHEN ex_no_data_found_file_item THEN
            RAISE_APPLICATION_ERROR(-20001, '请检查上传对象.');
    END GenerateHashFileName;
    PROCEDURE WriteBlobToUploadFile( P_HashFileName IN VARCHAR2,
                                     P_FileBlob IN BLOB,
                                     P_DirObject IN VARCHAR2 )
        IS
        v_buffer      RAW(32767);
        blob_length   INTEGER;
        out_file      UTL_FILE.FILE_TYPE;
        chunk_size    BINARY_INTEGER := 32767;
        blob_position INTEGER        := 1;
        ex_no_data_found_blob EXCEPTION;
    BEGIN
        apex_debug.message('--------UploadFileName-------');
        apex_debug.message(P_HashFileName);
        IF P_FileBlob IS NULL THEN
            RAISE ex_no_data_found_blob;
        END IF;
        -- 检索BLOB的大小
        blob_length := DBMS_LOB.GETLENGTH(P_FileBlob);
        --  打开要写入blob的位置的句柄
        -- 注意:“wb”参数表示“以字节模式写入”，并且只有可在UTL_FILE pkg中使用Oracle 10g或更高版本。
        -- 使用'w'代替pre Oracle 10q数据库。
        out_file := UTL_FILE.FOPEN(P_DirObject,
                                   P_HashFileName,
                                   '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(P_FileBlob, 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);
    EXCEPTION
        WHEN ex_no_data_found_blob THEN
            RAISE_APPLICATION_ERROR(-20000, '请检查文件对象。');
        WHEN utl_file.invalid_path THEN
            RAISE_APPLICATION_ERROR(-20011, 'INVALID_PATH: 文件位置或文件名无效。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.invalid_mode THEN
            RAISE_APPLICATION_ERROR(-20022, 'INVALID_MODE: FOPEN中的open_mode参数无效。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.invalid_filehandle THEN
            RAISE_APPLICATION_ERROR(-20022, 'INVALID_FILEHANDLE: 文件句柄无效。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.invalid_operation THEN
            RAISE_APPLICATION_ERROR(-20033, 'INVALID_OPERATION: 文件无法打开或按要求操作。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.read_error THEN
            RAISE_APPLICATION_ERROR(-20044, 'READ_ERROR: 期间发生操作系统错误读操作。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.write_error THEN
            RAISE_APPLICATION_ERROR(-20055, 'WRITE_ERROR: :操作系统错误写操作。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;
        WHEN utl_file.internal_error THEN
            RAISE_APPLICATION_ERROR(-20066, 'INTERNAL_ERROR: PL/SQL中未指定的错误。');
            IF UTL_FILE.IS_OPEN(out_file) THEN
                UTL_FILE.FCLOSE(out_file);
            END IF;


    END WriteBlobToUploadFile;
    FUNCTION ReadUploadFileToBlob( P_HashFileName IN varchar2,
                                   P_DirObject IN VARCHAR2 ) RETURN blob
        IS
        v_lob         BLOB;
        v_BFile       BFILE;
        v_lenght      number;
        v_src_offset  NUMBER := 1;
        v_dest_offset NUMBER := 1;
    BEGIN
        /**********************************************************************************************************************************
        2024/03/11 14:33
          依据文件名,读磁盘上的文件并返回二进制数据blob
         **********************************************************************************************************************************/

        dbms_lob.createtemporary(v_lob, FALSE, DBMS_LOB.SESSION);
        v_BFile := BFILENAME(P_DirObject, P_HashFileName);
        v_lenght := DBMS_LOB.GETLENGTH(v_BFile);

        dbms_lob.fileOpen(v_BFile);
        dbms_lob.loadblobfromfile(dest_lob => v_lob,
                                  src_bfile => v_BFile,
                                  amount => v_lenght,
                                  dest_offset => v_dest_offset,
                                  src_offset => v_src_offset);
        dbms_lob.fileClose(v_BFile);
        RETURN v_lob;
    EXCEPTION
        WHEN utl_file.invalid_path THEN
            RAISE_APPLICATION_ERROR(-20011, 'INVALID_PATH: 文件位置或文件名无效。');
        WHEN utl_file.invalid_mode THEN
            RAISE_APPLICATION_ERROR(-20022, 'INVALID_MODE: FOPEN中的open_mode参数无效。');
        WHEN utl_file.invalid_filehandle THEN
            RAISE_APPLICATION_ERROR(-20022, 'INVALID_FILEHANDLE: 文件句柄无效。');
        WHEN utl_file.invalid_operation THEN
            RAISE_APPLICATION_ERROR(-20033, 'INVALID_OPERATION: 文件无法打开或按要求操作。');
        WHEN utl_file.read_error THEN
            RAISE_APPLICATION_ERROR(-20044, 'READ_ERROR: 期间发生操作系统错误读操作。');
        WHEN utl_file.write_error THEN
            RAISE_APPLICATION_ERROR(-20055, 'WRITE_ERROR: :操作系统错误写操作。');
        WHEN utl_file.internal_error THEN
            RAISE_APPLICATION_ERROR(-20066, 'INTERNAL_ERROR: PL/SQL中未指定的错误。');
    END ReadUploadFileToBlob ;
    PROCEDURE blobToDownload( P_FileName IN VARCHAR2,
                              p_blob IN blob,
                              P_MimeType IN VARCHAR2 DEFAULT 'text/plain' )
        IS
        v_lob BLOB := p_blob;
    BEGIN
        -- 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-Type:  text/html; charset=utf-8' );
        htp.p('Content-length: ' || sys.dbms_lob.getlength(v_lob));
        -- htp.p('Content-Disposition: attachment; filename="' || P_FileName || '"');
        htp.p(q'~Content-Disposition: attachment; filename*=UTF-8''~' || utl_url.escape(P_FileName, TRUE, 'UTF-8'));
        --2023-07-12
        -- htp.p('Cache-Control: max-age=3600'); -- 浏览器缓存一个小时，根据需要进行调整
        owa_util.http_header_close;
        wpg_docload.download_file(v_lob);
        apex_application.stop_apex_engine;
    END blobToDownload ;
    PROCEDURE ReadUploadFileToDownload( P_FileName IN VARCHAR2,
                                        P_HashFileName 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
        v_lob := ReadUploadFileToBlob(P_HashFileName=>P_HashFileName,
                                      P_DirObject=>P_DirObject);
        blobToDownload(P_FileName=>P_FileName,
                       p_blob => v_lob,
                       P_MimeType=>P_MimeType);

    END ReadUploadFileToDownload;
    PROCEDURE DeleteUploadFile( P_HashFileName IN VARCHAR2, P_DirObject IN VARCHAR2 )
        IS
    BEGIN
        UTL_FILE.FREMOVE(P_DirObject, P_HashFileName);
    EXCEPTION
        -- WHEN utl_file.invalid_path THEN
        --    RAISE_APPLICATION_ERROR(-20011, 'INVALID_PATH: 文件位置或文件名无效。');
        --- WHEN utl_file.invalid_mode THEN
        --    RAISE_APPLICATION_ERROR(-20022, 'INVALID_MODE: FOPEN中的open_mode参数无效。');
        -- WHEN utl_file.invalid_filehandle THEN
        --    RAISE_APPLICATION_ERROR(-20022, 'INVALID_FILEHANDLE: 文件句柄无效。');
        -- WHEN utl_file.invalid_operation THEN

        --    RAISE_APPLICATION_ERROR(-20033, 'INVALID_OPERATION: 文件无法打开或按要求操作。');
        WHEN utl_file.read_error THEN
            RAISE_APPLICATION_ERROR(-20044, 'READ_ERROR: 期间发生操作系统错误读操作。');
        WHEN utl_file.write_error THEN
            RAISE_APPLICATION_ERROR(-20055, 'WRITE_ERROR: :操作系统错误写操作。');
        WHEN utl_file.internal_error THEN
            RAISE_APPLICATION_ERROR(-20066, 'INTERNAL_ERROR: PL/SQL中未指定的错误。');
    END DeleteUploadFile;
END UploadFilePackageV2;

3. 使用

获取下载地址

1
2
3
4
5
6
7
8
9


SELECT PAYMENT_ID,
    LISTAGG('<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

上传文件写成文件

 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


DECLARE
    P_Directory_obj  varchar2(2000) := :GLOBAL_UPLOAD_DIR_OBJ; -- 从全局变量中获取文件对象
    P_HashFilename   varchar2(400);
    P_SourceFilename varchar2(400);
BEGIN
    FOR atf IN (SELECT atf.*,
                DBMS_LOB.GETLENGTH(BLOB_CONTENT) len
                FROM apex_application_temp_files atf
                WHERE atf.application_id = :APP_ID
                AND atf.name IN (SELECT DISTINCT COLUMN_VALUE
                                FROM TABLE (apex_string.split(:P49_ATTACHMENT, ':')))) -- 附件多选
        LOOP
            -- 1.依据页项获取hash文件名和文件名
            UploadFilePackageV2.GenerateHashFileName(atf.NAME,
                                                    P_HashFilename,
                                                    P_SourceFilename);

            apex_debug.warn('----');
            apex_debug.warn(P_SourceFilename);
            apex_debug.warn(P_HashFilename);
            apex_debug.warn('----');
            apex_debug.warn(P_SourceFilename);
            -- 2.将二进制从apex_application_temp_files临时表转存到服务器磁盘上依据hash后的名字
            UploadFilePackageV2.WriteBlobToUploadFile(P_HashFileName => P_HashFilename,
                                                    P_FileBlob => atf.BLOB_CONTENT,
                                                    P_DirObject => P_Directory_obj);
            -- 3.记录本次上传的记录 fk, 文件名,hash文件名,mime_type
            INSERT INTO xxxxx (fk_id, ATTACHMENT_FILENAME, ATTACHMENT_FILENAME_hash, ATTACHMENT_MIMETYPE, REMARK,	Attachment_Length)
            VALUES (:P49_FK_ID, P_SourceFilename, P_HashFilename, atf.MIME_TYPE, :P49_REMARK,atf.len);


        END LOOP;
END;

下载附件

前提条件处理条件请求=DownloadUploadFile OsXM1-2024-08-16-09-47-38

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


DECLARE
    file_hash_name varchar2(2000);
        file_name varchar2(2000);
    file_mimetype  varchar2(2000);
BEGIN
    -- 更据id 获取hash后的名字和mimetype
    SELECT MAX(ATTACHMENT_FILENAME_HASH), MAX(ATTACHMENT_MIMETYPE),MAX(ATTACHMENT_FILENAME)
    INTO file_hash_name,file_mimetype,file_name
    FROM xxxxx
    WHERE ID = :P2_ID;
    -- 调用读取和下载过程
    UploadFilePackagev2.ReadUploadFileToDownload(P_HashFileName => file_hash_name,
                                                P_FileName=>file_name,
                                                P_DirObject => :GLOBAL_UPLOAD_DIR_OBJ,
                                                P_MimeType =>file_mimetype);
END;

其他类似操作
1. 图片呈现
2. 打包下载