How can I upload a file (xml specifically) onto the database server on a certain location specified by an Oracle directory? It seems that fileUpload stores the file in a clob in some table, isn't it? Do I have to save it, then, from the table into my destination directory? What's the best way to have my file in that directory knowing that this is a file uploaded by the user at runtime. Afterwards, a series of processing steps are executed depending on the content.

asked 28 Jan '13, 09:48

cezarp's gravatar image

cezarp
1516
accept rate: 0%

edited 28 Jan '13, 09:58


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

link

answered 28 Jan '13, 10:55

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

edited 28 Jan '13, 11:03

It appears that something goes wrong. I am uploading various files. For example, one is called test1.xml (4.5k) and the other is called text2.xml (180k). First one is uploaded into my destination database server directory, but the latter triggers an error. In formspider I get a popup saying "Cannot find Application 'myApp'". I tried to catch the error putting api_application.showpopupmessage(sqlcode || sqlerrm); in the exception block, but no luck. No popup is beeing shown. Any hints please? Thanks

(01 Feb '13, 13:41) cezarp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×4
×4
×2
×1

Asked: 28 Jan '13, 09:48

Seen: 1,165 times

Last updated: 01 Feb '13, 13:41


© Copyright Gerger 2017