Upload and
Download of Files from Server
Download of Files from Server
Step 1:
Create
a File browse item.
a File browse item.
Step 2:
Create
a directory in database.
a directory in database.
Step 3:
Create
a procedure to upload file in the server.
a procedure to upload file in the server.
create
or replace PROCEDURE
“BLOB_TO_FILE” (
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;
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
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);
file_name, ‘wb’, 32767);
—
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.Read(p_data, l_amount, l_pos,
l_buffer);
l_buffer);
IF l_buffer IS NOT NULL THEN
UTL_FILE.put_raw(l_out_file, l_buffer,
True);
True);
END IF;
l_pos := l_pos + l_amount;
END LOOP;
—
UTL_FILE.fclose(l_out_file);
————–
END LOOP;
EXCEPTION
WHEN
OTHERS THEN
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.
a button in page for submitting the page.
Step 5:
Create
a page process to call the upload file procedure.
a page process to call the upload file procedure.
DECLARE
P_FILE_NAME
VARCHAR2(200);
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);
: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);
updating File ‘|| SQLERRM);
END;
END IF;
END;
Step 5:
Create
a button to download the existing file in the server.
a button to download the existing file in the server.
Step 6:
Create
a procedure to download file
a procedure to download file
create
or replace PROCEDURE
“DOWNLOAD_MY_FILE” (
or replace PROCEDURE
“DOWNLOAD_MY_FILE” (
iv_n_observation_id IN NUMBER,
iv_n_file IN NUMBER)
AS
v_length
NUMBER;
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);
:= bfilename(‘PS_TEMP_DIR’, v_file_name);
–raise_application_error(-20001,
‘Lob_loc—>’||Lob_loc);
‘Lob_loc—>’||Lob_loc);
v_length
:= dbms_lob.getlength(Lob_loc);
:= dbms_lob.getlength(Lob_loc);
—
raise_application_error(-20001, ‘v_length—>’||v_length);
raise_application_error(-20001, ‘v_length—>’||v_length);
owa_util.mime_header(‘application/octet’,
False);
False);
htp.p(‘Content-length: ‘ || v_length);
htp.p(‘Content-Disposition: attachment;
filename=”‘ || SUBSTR(v_file_name, INSTR(v_file_name, ‘/’) + 1) ||
‘”‘);
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);
— ‘||SQLERRM);
apex_application.stop_apex_engine;
END
download_my_file;
download_my_file;
Step
7:
7:
Create
a page process to call the procedure using the download button
a page process to call the procedure using the download button
BEGIN
AP_SY_pr_download_file( iv_n_request_id =>
:P13_REQUEST_ID,
:P13_REQUEST_ID,
iv_v_file_typ => ‘EST’);
–rollback;
END;
By
Palani Kumar K
Recommended Posts
