Monday 2 January 2017

Upload and Download of Files from Server


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

No comments:

Post a Comment