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

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);

  -- 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, 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
  when others then
    -- close the file if something goes wrong.
    if utl_file.is_open(v_file) then
    end if;

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

        <fileUpload name="fileUpload"/>
        <button label="Upload" uploadFrom="mainPanel.fileUpload" uploadOnEvent="buttonPress">
            <buttonPress action="createFileFromUploadedBLOB" />

Hope this helps,


answered 28 Jan '13, 10:55

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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



Asked: 28 Jan '13, 09:48

Seen: 1,809 times

Last updated: 01 Feb '13, 13:41

© Copyright Gerger 2017