This discussion is archived
0 Replies Latest reply: Jan 13, 2013 7:40 AM by Ramani_vadakadu RSS

Not Download file from oracle directory

Ramani_vadakadu Journeyer
Currently Being Moderated
I have used the Save to Disk as plug in from this site. {http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/savetodisk_167.html}

I upload file to server location as <b> \\db11g\misappl\kac\my_dir</b> its saved

*download plug in and create one item with (DB Directory Name,&P2_DOC_FILE_NAME.,rest of thinks empty)

*Create process -On Submit,after Comp&Validation with(Browse item (P2_DOC_FILE),directory (MY_DIR),filename(P2_DOC_MIME_TYPE),Item with file Name(P2_DOC_FILE_NAME),HashValue(P1_FILEHASH) <b> Condition when  Create button</b>

*write_blob_to_file(:P2_DOC_ID,'MY_DIR');

*Its working fine', stored the browsed file in server directory.
-----------
<b><font color='red'>How to DML actions for the stored directory file?can i store multiple file at the time with different Sequence number?</font></b>
--------------
create or replace 
PROCEDURE write_blob_to_file (
   p_file_id   IN NUMBER
  ,p_dir       IN VARCHAR2
)
IS
   l_blob            BLOB;
   l_blob_length     INTEGER;
   l_out_file        UTL_FILE.file_type;
   l_buffer          RAW (32767);
   l_chunk_size      BINARY_INTEGER := 32767;
   l_blob_position   INTEGER := 1;
   l_file_name       DMS_MASTER.DOC_FILE_NAME%TYPE;
BEGIN
   -- Retrieve the BLOB for reading
   SELECT DOC_FILE,DOC_FILE_NAME
     INTO l_blob, l_file_name
     FROM DMS_MASTER
    WHERE DOC_ID = p_file_id;

   -- Retrieve the SIZE of the BLOB
   l_blob_length := DBMS_LOB.getlength (l_blob);

   -- Open a handle to the location where you are going to write the BLOB 
   -- to file.
   -- NOTE: The 'wb' parameter means "write in byte mode" and is only
   --       available in the UTL_FILE package with Oracle 10g or later
   l_out_file :=
      UTL_FILE.fopen (
         p_dir
        ,l_file_name
        ,'wb' -- important. If ony w then extra carriage return/line brake
        ,l_chunk_size
      );

   -- Write the BLOB to file in chunks
   WHILE l_blob_position <= l_blob_length
   LOOP
      IF l_blob_position + l_chunk_size - 1 > l_blob_length
      THEN
         l_chunk_size := l_blob_length - l_blob_position + 1;
      END IF;

      DBMS_LOB.read (
         l_blob
        ,l_chunk_size
        ,l_blob_position
        ,l_buffer
      );
      UTL_FILE.put_raw (l_out_file, l_buffer, TRUE);
      l_blob_position := l_blob_position + l_chunk_size;
   END LOOP;

   -- Close the file handle
   UTL_FILE.fclose (l_out_file);
END write_blob_to_file;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points