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