fileUpload

Hi Cezar,

You don't have to save the uploaded CLOB in any database table, it's possible to retrieve the uploaded file and create a file onto the database server on the fly.

You can retrieve the uploaded file as a BLOB using the api_component.getvaluebl API. We prefer BLOB since it's more generic and compatible with all file types. Then, you can create a file onto your database server from this BLOB using the utl_file package.

The procedure below retrieves the uploaded file from a fileUpload component named "mainPanel.fileUpload" and creates a file onto the database server using a database directory named "DBDIR":

procedure createFileFromUploadedBLOB is
  v_file                utl_file.file_type;
  v_buffer              raw(32767);
  v_amount_nr           number := 32767;
  v_pos_nr              number := 1;
  v_blob                blob;
  v_blobLen_nr          number;
  v_dbDirectoryName_tx  varchar2(4000) := 'DBDIR';
  v_fileName_tx         varchar2(4000);
begin

  -- use the name of the uploaded file as the file name
  v_fileName_tx := api_component.getuploadedfilename('mainPanel.fileUpload');
  -- retrieve uploaded file as blob
  v_blob        := api_component.getvaluebl('mainPanel.fileUpload');

  v_blobLen_nr := dbms_lob.getlength(v_blob);
  -- open the destination file
  v_file := utl_file.fopen(v_dbDirectoryName_tx, v_fileName_tx, 'w', 32767);

  -- read chunks of the BLOB and write them to the file
  while v_pos_nr < v_blobLen_nr loop
    dbms_lob.read(v_blob, v_amount_nr, v_pos_nr, v_buffer);
    utl_file.put_raw(v_file, v_buffer, true);
    v_pos_nr := v_pos_nr + v_amount_nr;
  end loop;

  -- close the file
  utl_file.fclose(v_file);
exception
  when others then
    -- close the file if something goes wrong.
    if utl_file.is_open(v_file) then
      utl_file.fclose(v_file);
    end if;
    raise;
end;

You may use the following panel XML to experiment the procedure above:

<panel>
  <tableLayout>
    <row>
      <cell>
        <fileUpload name="fileUpload"/>
      </cell>
      <cell>
        <button label="Upload" uploadFrom="mainPanel.fileUpload" uploadOnEvent="buttonPress">
          <events>
            <buttonPress action="createFileFromUploadedBLOB" />
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Hope this helps,
Ibrahim