Upload and
Download of Files from Server
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;


By 
Palani Kumar K
Recommended Posts

Start typing and press Enter to search