Thursday, 31 March 2011

AJAX File Upload


Ok, so I looked into this a while ago, and had all the idea's in my head, but just never got around to putting something together.

Some of the newer HTML5 File API's allow you to access the file contents (base64 is what we like) - by using the FileReader object. Unfortunately, not all browsers have support for it, so you would not be able to use it accross the board. There are a lot of resources on the topic. If interested, check out: http://www.html5rocks.com/tutorials/file/dndfiles/ and https://developer.mozilla.org/en/Using_files_from_web_applications. There is another plugin out there, which supports ajax uploads with IE, but unfortunately it's commercial; which is another reason I was keen to get this happening.

So anyway, I've created a demo of this - which you can check out at: http://apex.oracle.com/pls/apex/f?p=45448:afu

I used the following table to get started:


CREATE TABLE PLUGIN_BLOB 
(
  ID NUMBER NOT NULL 
, MIME_TYPE VARCHAR2(200) 
, FILENAME VARCHAR2(200) 
, DATA BLOB 
, SOME_FK NUMBER 
, CONSTRAINT PLUGIN_BLOB_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

create sequence plugin_blob_seq;

CREATE OR REPLACE TRIGGER BI_PLUGIN_BLOB before
  INSERT ON "PLUGIN_BLOB" FOR EACH row BEGIN IF inserting THEN IF :NEW."ID" IS NULL THEN
  SELECT PLUGIN_BLOB_SEQ.nextval INTO :NEW."ID" FROM dual;
END IF;
END IF;

I wont get to deep in to the nitty gritty, but basically, when you set it up, you specify some fields (most of which you can tell is from the table defined above):


At the moment, it has attributes to specify that the table has a foreign key column; I was also going to return the primary key from the insert statements into a page item (declared in an attribute), but I was having some issues in that no settings were available in the page item once I got to 9 attributes - so I let it pass for now. It might be that my development box is still on 4.01.00.03 - I will sus it out sometime later, or someone else can pickup from where i've left off ;-) The other thing is the fact that I've hard coded the button style and label, which may like to be customized.

So, if you want to test that the foreign key insertion is working properly, just set a number value to P6_SOME_FK through the URL, and it too will be populated - at least I hope so ;-)

I've attached one file thus far - which is the plugin export file. File: item_type_plugin_ts_ajaxfileupload.sql

This little project also gave me the opportunity to learn more about the apex.ajax.clob object - I saw someone post about Carls blog on the OTN forums, that contained the information about it; Pretty handy to know! See: http://carlback.blogspot.com/2008/04/new-stuff-4-over-head-with-clob.html

Saturday, 19 March 2011

Item Plugin Template


This is more a reference on how to create a basic item plugin - just a text field that is exactly the same as Text item type. There is a more detailed guide for those interested on the Oracle Learning Library - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r40/apexplugins/apexplugins_ll.htm

A lot of the values to use in your plugin can be obtained from the t_page_item record, with one exception - the name attribute. For this, you need to use the function get_input_name_for_page_item. The reason for this is that APEX needs to be able to map this input field to an item in session state. This gets the necessary element name (i.e. p_t02) and adds the additional hidden element to the page - p_arg_names.

With that in mind, the most basic of plugins (well, render function) - that adds no extra functionality is:


function render_file_item(
    p_item                in apex_plugin.t_page_item,
    p_plugin              in apex_plugin.t_plugin,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
AS
  l_result apex_plugin.t_page_item_render_result;
  l_name varchar2(200) := apex_plugin.get_input_name_for_page_item(false);
BEGIN
  if apex_application.g_debug then
        apex_plugin_util.debug_page_item (
            p_plugin              => p_plugin,
            p_page_item           => p_item,
            p_value               => p_value,
            p_is_readonly         => p_is_readonly,
            p_is_printer_friendly => p_is_printer_friendly );
    end if;

  sys.htp.p('
<input id="' || p_item.name || '" type="text" name="' ||l_name ||'" value="' || p_value || '" />');
  return l_result;
END;

Well, not a very helpful post, but gives the basic basis for a plugin item.

Thursday, 17 March 2011

Expand and Collapse all Tree Nodes


APEX comes with a nice region type which is a tree view. During the wizard creation, you have the option to add buttons to expand all and collapse all nodes - but after the fact, there is no option in the tree settings to add this functionality - so you either have to recreate the tree region specifying that option or have to know what you have to do in order to add in that functionality.

I came across this post on the OTN forums - http://forums.oracle.com/forums/message.jspa?messageID=4407552#4407552 - which give all the information about what you have to do to expand and collapse all nodes (I'm sure there are others, but that just happened to be the one I found).

This basic gist of it is this - there are two convenience functions to do the expanding and collapsing of all nodes - apex.widget.tree.expand_all(tree_id) and apex.widget.tree.collapse_all(tree_id), respectively - where tree_id is the unique identifier of the tree. You can either hard code it in (which is what is done when the buttons are created with the wizard, and in the examples on that post) or create a page item to reference the unique idenifier, and pass that in to the function call.

To find the unique identifier, you just have to view the page source. I find if you inspect the first node in the tree, the actual div with the tree id is 2 elements above. The other technique (which i have used as I find it avoids hard coding values) is to create a page item, and reference that item in the call to the javascript function(s).

So create a hidden page item (for example, P1_TREE_ID), followed by an item computation to run on page load with the source type of: SQL Query (return single value) and specify the source as:


select 'tree' || tree_id
from apex_application_page_trees
where page_id = :APP_PAGE_ID

Then, in my actual tree region, I specified region source as:

<a href="javascript:apex.widget.tree.expand_all('&P1_TREE_ID.')">Expand All</a> | <a href="javascript:apex.widget.tree.collapse_all('&P1_TREE_ID.')">Collapse All</a>


nb: This of course assumes your page only has one tree region on it, otherwise that computation will not work properly. Anyway, I can't think of a situation to use more than one tree on a page.

And there you have it. Expand and Collapse all links for your tree