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

3 comments:

  1. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you need to watch this video
    right away...

    (VIDEO) Get your ex back with TEXT messages?

    ReplyDelete
  2. BlueHost is ultimately the best hosting company with plans for all of your hosting needs.

    ReplyDelete
  3. Searching for the Ultimate Dating Website? Create an account to find your perfect match.

    ReplyDelete