Importing Files in Oracle Apex Using wwv_flow_files

Suppose you want to import a csv file content with custom database procedure in Oracle Apex using file browse page item. Follow these steps:

Create file browse page item in Apex and choose the wwv_flow_files option to store the file.

Then create the page process and choose after submit and execution of validation option and plsql code option for the process.

In plsql area write the following code to export csv file from database (wwv_flow_files view) to server drive and then call your custom procedure to import that file's content into a table.

DECLARE
   v_upl_blob BLOB;
  vstart     Number             := 1;
   bytelen    Number             := 32000;
   len        Number;
   my_vr      Raw (32000);
   x          Number;
   l_output   Utl_file.file_type;
   erout varchar2(1000);
BEGIN
   SELECT blob_content
     INTO v_upl_blob
     FROM wwv_flow_files
    WHERE name = :P25_FB;
-- :p25_fb is the file browse item in page
LEN := Dbms_lob.getlength(V_UPL_BLOB);
l_output := Utl_File.fopen ('MY_FILES', :P25_FB, 'wb', 32760);
   vstart := 1;
   bytelen := 32000;
   IF len < 32760
   Then
      Utl_File.put_raw (l_output, V_UPL_BLOB);
      Utl_File.fflush (l_output);
   Else                                                                                                                             -- write in pieces
      vstart := 1;

      WHILE vstart < len And bytelen > 0
      LOOP
         Dbms_lob.Read (V_UPL_BLOB, bytelen, vstart, my_vr);
         Utl_File.put_raw (l_output, my_vr);
         Utl_File.fflush (l_output);
         -- set the start position for the next cut
         vstart := vstart + bytelen;
         -- set the end position if less than 32000 bytes
         x := x - bytelen;

         IF x < 32000
         Then
            bytelen := x;
         END IF;
      END LOOP;
   END IF;

   Utl_File.fclose (l_output);

   DELETE FROM wwv_flow_files
    WHERE name = :P25_FB;
COMMIT;
--- call your custom database procedure to import...
yourcustomprocedure(:P25_FB);
EXCEPTION   When Others
   Then
     IF Utl_File.is_Open(l_output) Then
      Utl_File.fclose (L_OUTPUT);
     END IF;
raise;
END;