Introduction :
Oracle Application Express applications support the ability to upload and download files stored in the database.
Implementation :
Step 1:
Create a File browse item.
Step 2:
Create a directory in database.
Step 3:
Create a procedure to upload file in the server.
Create or replace PROCEDURE “BLOB_TO_FILE” (
p_file_name IN VARCHAR2)
IS
l_out_file UTL_FILE.file_type;
l_buffer Raw(32767);
l_amount Binary_Integer := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
p_data BLOB;
file_name VARCHAR2(256);
BEGIN
FOR rec IN
(SELECT MAX(ID) ID FROM HTMLDB_APPLICATION_FILES WHERE Name = p_file_name
)
LOOP
SELECT BLOB_CONTENT,
filename
INTO p_data,
file_name
FROM HTMLDB_APPLICATION_FILES
WHERE ID = rec.ID;
—
l_blob_len := DBMS_LOB.getlength(p_data);
l_out_file := UTL_FILE.fopen(‘PS_TEMP_DIR’, file_name, ‘wb’, 32767);
—
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.Read(p_data, l_amount, l_pos, l_buffer);
IF l_buffer IS NOT NULL THEN
UTL_FILE.put_raw(l_out_file, l_buffer, True);
END IF;
l_pos := l_pos + l_amount;
END LOOP;
—
UTL_FILE.fclose(l_out_file);
————–
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_out_file) THEN
UTL_FILE.fclose(l_out_file);
END IF;
END;
Step 4:
Create a button in page for submitting the page.
Step 5:
Create a page process to call the upload file procedure.
DECLARE
P_FILE_NAME VARCHAR2(200);
lv_v_file_name VARCHAR2(100);
BEGIN
P_FILE_NAME := NULL;
IF :P13_ESTIMATE_FILE IS NOT NULL THEN
BEGIN
SELECT FILENAME
INTO lv_v_file_name
FROM HTMLDB_APPLICATION_FILES
WHERE NAME=:P13_ESTIMATE_FILE;
EXCEPTION
WHEN no_data_found THEN
lv_v_file_name := NULL;
END;
BEGIN
SELECT ‘VMS_’
||:P13_REQUEST_ID
||’_’
||lv_v_file_name
INTO lv_v_file_name
FROM dual;
EXCEPTION
WHEN no_data_found THEN
lv_v_file_name := NULL;
END;
BLOB_TO_FILE( in_file_name => :P13_ESTIMATE_FILE);
–rollback;
BEGIN
UPDATE AP_SY_VM_TB_REQ_HDR vrh
SET ESTIMATE_FILE_NAME=:P13_ESTIMATE_FILE
WHERE REQUEST_ID =:P13_REQUEST_ID;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20158,’Error in updating File ‘|| SQLERRM);
END;
END IF;
END;
Step 5:
Create a button to download the existing file in the server.
Step 6:
Create a procedure to download file.
Create or replace PROCEDURE “DOWNLOAD_MY_FILE” (
iv_n_observation_id IN NUMBER,
iv_n_file IN NUMBER)
AS
v_length NUMBER;
v_file_name VARCHAR2(2000);
Lob_loc Bfile;
BEGIN
BEGIN
SELECT attachment_filename
INTO v_file_name
FROM orw_tb_report_atchmts F
WHERE attachment_id = iv_n_file
AND observation_id = iv_n_observation_id;
EXCEPTION
WHEN no_data_found THEN
v_file_name := NULL;
END;
IF v_file_name IS NOT NULL THEN
Lob_loc := bfilename(‘PS_TEMP_DIR’, v_file_name);
–raise_application_error(-20001, ‘Lob_loc—>’||Lob_loc);
v_length := dbms_lob.getlength(Lob_loc);
— raise_application_error(-20001, ‘v_length—>’||v_length);
owa_util.mime_header(‘application/octet’, False);
htp.p(‘Content-length: ‘ || v_length);
htp.p(‘Content-Disposition: attachment; filename=”‘ || SUBSTR(v_file_name, INSTR(v_file_name, ‘/’) + 1) || ‘”‘);
owa_util.http_header_close;
wpg_docload.download_file(Lob_loc);
apex_application.stop_apex_engine;
END IF;
exception
when others then
raise_application_error(-20002, SQLCODE||’ — ‘||SQLERRM);
apex_application.stop_apex_engine;
END download_my_file;
Step 7:
Create a page process to call the procedure using the download button.
BEGIN
AP_SY_pr_download_file( iv_n_request_id => :P13_REQUEST_ID,
iv_v_file_typ => ‘EST’);
–rollback;
END;
Conclusion:
This Package will help us to Upload and Download the Files from Server.
