Upload Document and Download Document In Directory
1. Create Directory in Database and provide grant to directory
>>> create directory dir_xxapex as '/apexshare';
>>> grant read,write on directory dir_xxapex to xx_apex;
Also Provide folder permission to pubic
2. Create Table
CREATE TABLE xxapex_documents (
do_doc_no NUMBER,
do_doc_reftable VARCHAR2(250),
do_doc_refid NUMBER,
do_doc_file BLOB,
do_file_name VARCHAR2(250),
do_mime_type VARCHAR2(250),
do_created_by VARCHAR2(255),
do_created_date TIMESTAMP,
do_last_updated_by VARCHAR2(255),
do_last_updated_date TIMESTAMP
)
3.Create Report with Form page in Apex Using the above table. (Page 39)
>>> Create Process on submit to upload file to direcoty
Add below code in the process
---------------------------------------
xxapex_file_pkg.file_upload(
p_filedoc => :P39_DO_DOC_FILE,
p_dir_name => 'DIR_XX_APEX',
p_filename => :P39_DO_FILE_NAME,
p_mimetype => :P39_DO_MIME_TYPE
);
After that create Auto DML Form process to save record in table.
Note: In Last you can find full package code.
4. For Download the uploaded file create blank page with normal template option.
>>> Create Process in Before header to download the file. ( page no :40)
Add Below Code in Process
------------------
xxapex_file_pkg.file_download (
p_dir_name => 'DIR_XX_APEX',
p_filename => :P40_FILENAME,
p_mimetype => :P40_MIME_TYPE
);
Create 2 page item filename and mimetype to hold the values.
Now changes the setting in Page 39 in Report download column.
create or replace PACKAGE xxapex_file_pkg AS
PROCEDURE file_upload (
p_filedoc IN VARCHAR2,
p_dir_name IN VARCHAR2,
p_filename OUT VARCHAR2,
p_mimetype OUT VARCHAR2
);
PROCEDURE file_download (
p_dir_name IN VARCHAR2,
p_filename IN VARCHAR2,
p_mimetype IN VARCHAR2
);
END xxapex_file_pkg;
Package Body :
create or replace PACKAGE BODY xxapex_file_pkg AS
PROCEDURE file_upload (
p_filedoc IN VARCHAR2,
p_dir_name IN VARCHAR2,
p_filename OUT VARCHAR2,
p_mimetype OUT VARCHAR2
) IS
lv_blob_content apex_application_temp_files.blob_content%TYPE;
lv_filename apex_application_temp_files.filename%TYPE;
lv_mime_type apex_application_temp_files.mime_type%TYPE;
buffer RAW(1024);
offset PLS_INTEGER := 1;
filelength PLS_INTEGER;
amount PLS_INTEGER := 1024;
fhandle utl_file.file_type;
BEGIN
/* Get File Info from temp file view */
BEGIN
SELECT
blob_content,
filename,
mime_type
INTO
lv_blob_content,
lv_filename,
lv_mime_type
FROM
apex_application_temp_files
WHERE
upper(name) = upper(p_filedoc);
EXCEPTION
WHEN no_data_found THEN
lv_blob_content := NULL;
lv_filename := NULL;
lv_mime_type := NULL;
WHEN OTHERS THEN
lv_blob_content := NULL;
lv_filename := NULL;
lv_mime_type := NULL;
END;
IF
lv_blob_content IS NOT NULL
AND lv_filename IS NOT NULL
THEN
BEGIN
filelength := dbms_lob.getlength(lv_blob_content);
fhandle := utl_file.fopen(upper(p_dir_name), lv_filename, 'wb');
LOOP
EXIT WHEN offset > filelength;
dbms_lob.read(lv_blob_content, amount, offset, buffer);
utl_file.put_raw(fhandle, buffer, TRUE);
offset := offset + amount;
END LOOP;
utl_file.fclose(fhandle);
p_filename := lv_filename;
p_mimetype := lv_mime_type;
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(fhandle) THEN
utl_file.fclose(fhandle);
END IF;
RAISE;
END;
ELSE
apex_error.add_error(
p_message => 'File Not found.',
p_display_location => apex_error.c_inline_in_notification
);
END IF;
END;
--------------------------------------------------------------------------------
PROCEDURE file_download (
p_dir_name IN VARCHAR2,
p_filename IN VARCHAR2,
p_mimetype IN VARCHAR2
)IS
l_bfile BFILE;
l_blob BLOB;
BEGIN
dbms_lob.createtemporary(l_blob,TRUE);
l_bfile := BFILENAME(UPPER(p_dir_name),p_filename);
dbms_lob.fileopen(l_bfile,DBMS_LOB.file_readonly);
dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
owa_util.mime_header(NVL(p_mimetype,'application/octet'),FALSE);
-- htp.p('Content-Length: '||DBMS_LOB.GETLENGTH(l_blob));
-- htp.p('Content-Disposition: attachment; filename="'||p_filename||'"');
owa_util.http_header_close;
WPG_DOCLOAD.download_file(l_blob);
dbms_lob.fileclose(l_bfile);
END;
END xxapex_file_pkg;
You can upload test after performing all above steps.
Thank You.
Comments
Post a Comment