Tuesday, August 28, 2012

How to Send/Upload a CLOB from the Browser to APEX via AJAX

Today Alistair Lang asked on Twitter "how do I pass in a CLOB to an on-demand process using AJAX in APEX?". I had this same questions a few months ago when I was working on uploading files using AJAX into APEX.

It turns out you can't use the standard addParam APEX JavaScript method (hopefully this will change in 4.2). Instead you need call a different function which will store the CLOB into a special APEX collection then process the CLOB from that collection. Here's a breakdown of what needs to happen:

- Send the CLOB from the browser to APEX. It will be stored in the CLOB001 column in the collection "CLOB_CONTENT".
- Once the CLOB is sent to APEX call your On Demand process (i.e. AJAX request) to run some PL/SQL code. This PL/SQL code will need to retrieve the CLOB value from the collection

Here's an example

On Demand Process: On your page create an On Demand process called "MY_PROCESS". In the code enter the following:
DECLARE
  l_clob CLOB;
BEGIN
  SELECT clob001 
  INTO l_clob
  FROM apex_collections 
  WHERE collection_name = 'CLOB_CONTENT';
  
  -- Now you can process the CLOB using l_clob
END;
JavaScript Code: This can be stored either in a Dynamic Action or custom JS code:
/**
 * Code to run once the upload is done
 * 
 * Clob is now accessible in the apex_collections view:
 * SELECT collection_name, seq_id, clob001 FROM apex_collections 
 * WHERE collection_name = 'CLOB_CONTENT';
 *  - Note: The collection name "CLOB_CONTENT" is not modifiable
 * 
 * Use this function to make an AJAX request to trigger 
 * an On Demand Process (i.e. run some PL/SQL code)
 */
function clubUploadDone(){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=MY_PROCESS',$v('pFlowStepId'));
  //Optional: pass some additional values get.addParam('x01','some data');
  gReturn = get.get();
}

/**
 * Send clob to APEX (will be stored in the apex_collection "CLOB_CONTENT"
 */
var clobObj = new apex.ajax.clob(
  //Callback funciton. only process CLOB once it's finished uploading to APEX
  function(p){
    if (p.readyState == 4){
      clubUploadDone();
    }
  });
  
clobObj._set('Put clob content here'); //Sends the data to Oracle/APEX collection

It's important to note that there is only one area where the CLOB data is stored so each time you send a new CLOB it will erase the older value in the collection. If you're sending multiple CLOBS sequentially you need to handle it accordingly. A good example of this is if you're uploading multiple files via a drag & drop interface.

5 comments:

  1. Thank You for this concise explanation. Using this example, I was able to put together the CLOB upload that I needed in no time.

    ReplyDelete
  2. Hi Martin

    Is there a similar option for BLOBs (such as sending images from the browser to APEX) - using DA ?

    Stefan

    ReplyDelete
    Replies
    1. Hi Stefan,

      In JavaScript (i.e. from the browser) you can use something like this:

      var clobObj = new apex.ajax.clob(
      //Callback funciton. only process CLOB once it's finished uploading to APEX
      function(p){
      if (p.readyState == 4){
      //Upload Done

      //Call some Application Process once the upload is done
      var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=UPLOAD_CANVAS',$v('pFlowStepId'))

      //Optional: pass some additional values get.addParam('x01','some data');
      get.addParam('x01',mimeType);
      get.addParam('x02',fileExtension);
      gReturn = get.get();
      }
      }
      );

      //img64 is the base64 of the file/image (you'll need to set this yourself)
      clobObj._set(img64); //Sends the data to Oracle/APEX collection


      In PL/SQL you can convert a CLOB (base64) to a BLOB from the Application Process using apex_web_service.clobbase642blob http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_web_service.htm#BABHIEJB

      Delete
  3. Hello Martin,
    I have been able to get this work in chrome.But in Firefox it does not work .The clob_content collection never gets populated when using Firefox.Have you also faced this?

    Thanks
    Ceejay

    ReplyDelete
    Replies
    1. Not that I know of. I think I should write an update post using the new apex JS APIs.

      Delete