Friday, 2 December 2011

Re-Sequence Tabular Form


Well, a while back I was looking at what's provided in the application builder. On a page with a number of application items - in tree view, if you right click and select Drag & Drop Layout, and then switch tabs to Reorder Items, you are presented with a tabular form. On the far right you'll will see little up and down arrow image icons, and after selecting, allows you to easily move items up or down. This is not something that comes standard, and in my opinion, something that is very useful.

Once again, if we look closely, we should be able to easily replicate that functionality.

Step one, I will create a table - a common one of the top of my head is statuses, so i will create a status table with 3 columns: status_id, name, sequence:


CREATE table "STATUS" (
    "STATUS_ID"  NUMBER,
    "NAME"       VARCHAR2(20),
    "SEQUENCE"   NUMBER,
    constraint  "STATUS_PK" primary key ("STATUS_ID")
)
/

CREATE sequence "STATUS_SEQ"
/

CREATE trigger "BI_STATUS"
  before insert on "STATUS"
  for each row
begin
  if :NEW."STATUS_ID" is null then
    select "STATUS_SEQ".nextval into :NEW."STATUS_ID" from dual;
  end if;
end;
/


Then a create a new page with a tabular form - all pretty standard stuff.

Since the up and down arrows are kind of links, i'm going to add a column link column type. If you go into the report attributes, on the right hand side of the page, you will see a little box with the heading Tasks. In there are four options:

  • Show Query Source
  • Add Column Link
  • Add Derived Column
  • Unsubscribe Templates

As you can see, the second one is the one we want. So click that, specify Display as: Standard Report Column; in the Column Attributes section (this is so the img tags are rendered); and in the HTML Expression add the following:


<img class="pb" src="/i/htmldb/icons/up_arrow.gif" alt="" /><img class="pb" src="/i/htmldb/icons/down_arrow.gif" alt="" />


Which is just what we copied from inspecting the re-order items page.

Save the changes, move that column to the last item in the report columns, and run the page, you should see the two arrows in your report.

Next step, is to make them actually re-order the rows. If you inspect the arrows in the re-order items page, you will be able to see that they have a click event attached to them. Which you can see has the following code:


b.row.down(this)

And that it is in the script apex_4_1.js. This particular script has been minified, so it will be very hard to look at the code to see what is actually going on here. Fortunately, when you install apex, you get the uncompressed version of the code. So if you go into the images folder there is one with exactly the same filename. If we look in this and search for the text b.row.down - you won't find anything, most likely because the b variable is just one that has been minified to make the script smaller. SO instead, just search for row.down. Before long, you will find the actual function that re-orders rows. Without going into the specifics of that function, we can build the following two functions:


function moveRowDown(row,idx){
 var lastRow = false;
 currRow = $x_UpTill(row, 'TR');
 currTable = currRow.parentNode;
 ie_RowFixStart(currRow);
 nextRow = currRow.nextSibling;

 while(nextRow != null){
  if(nextRow.nodeType ==1){break;}
  nextRow = nextRow.nextSibling;
 }

 var currRowSeqEle = $('input[name="' + idx + '"]', currRow)[0];
 var nextRowSeqEle =$('input[name="' + idx + '"]', nextRow)[0];
 var currValue = currRowSeqEle.value;
 var nextValue = nextRowSeqEle.value;

 if(nextRow ){
  currRowSeqEle.value = nextValue;
  nextRowSeqEle.value = currValue;
  currTable.insertBefore(currRow, nextRow.nextSibling);
 }else {
  //if you want to implement that it goes back to top. I'm not bothering.
  //currTable.insertBefore(currRow, currTable.getElementsByTagName('TR')[1]);
 }
}

function moveRowUp(row, idx){
 var firstRow = false;
 currRow = $x_UpTill(row, 'TR');
 currTable = currRow.parentNode;
 prevRow = currRow.previousSibling;
 while(prevRow != null){
  if(prevRow.nodeType ==1){break;}
  prevRow = prevRow.previousSibling;
 }

 var currRowSeqEle = $('input[name="' + idx + '"]', currRow)[0];
 var prevRowSeqEle =$('input[name="' + idx + '"]', prevRow)[0];
 var currValue = currRowSeqEle.value;
 var prevValue = prevRowSeqEle.value;

 if(prevRow != null && prevRow.firstChild.nodeName != 'TH'){

  currRowSeqEle.value = prevValue;
  prevRowSeqEle.value = currValue;
  currTable.insertBefore(currRow, prevRow);
 } else {
  //if you want to implement that it goes back to top. I'm not bothering.
  //currTable.appendChild(currRow);
    }
}


A couple of things to note about this code.

The original version - if someone pushes down when at the bottom it will re-go to the top; if someone pushes up when at the top, it will re-go to the bottom. I decided against this for simplicity reasons - For one if one a multi page tabular form, i you push down at the bottom, should it go to the next page or back to the beginning? Having to cycle through all the sequences to re-calculate them - this isn't a big deal at all, but i've just decided i prefer that when they reach the bottom, that is that.

The functions both accept two parameters - the row, that is, the element being pushed, And also the f0x index. Each tabular form column gets an f0x index which is in the name attribute. In our case, it is f04, so i will be passing that value.

So paste this code somewhere - in your page header; in your page template; in your personal javascript library. Now, go back and edit the column link to add an onclick event to both img elements. So they should look like:


<img class="pb" onclick="moveRowUp(this, 'f04')" src="/i/htmldb/icons/up_arrow.gif" alt="" /><img class="pb" onclick="moveRowDown(this, 'f04')" src="/i/htmldb/icons/down_arrow.gif" alt="" />

And voila. Now you can re-sequence your items. The only thing you are likely wanting to do is hide the sequence column (hidden display type). I've left mine as display simply so i can see the sequence working, for example purposes.

See an example here: http://apex.oracle.com/pls/apex/f?p=45448:9

..

Another solution is not to deal with the sequence number on client side, and just have an on submit process that re-orders them, such as (un-tested):


for i in 1..APEX_APPLICATION.g_f01.COUNT LOOP
--update table set seq_column = i;
END LOOP;

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

Thursday, 10 February 2011

Google Visualization API (Org Chart)

Wanted to have a go at making a family tree, and came across this API. It is worth noting this particular chart doesn't allow you to specify more than one node for the parent, so not exactly perfect for a family tree. See: http://code.google.com/p/google-visualization-api-issues/issues/detail?id=162.

I believe it worthwhile to read the getting started guide for google visualizations: http://code.google.com/apis/visualization/documentation/using_overview.html and the actual guide for using the organizational chart: http://code.google.com/apis/visualization/documentation/gallery/orgchart.html.

Why use this over the charts provided in APEX? For one thing, there is no organizational chart available, to my knowledge. For another, it is not flash ;-) That can only be a good thing!

Alright, down to business. Firstly create a table to store the data:


CREATE TABLE "PERSON"
  (
    "PERSON_ID" NUMBER NOT NULL ENABLE,
    "NAME"      VARCHAR2(200 BYTE),
    "PARENT_ID" NUMBER,
    CONSTRAINT "PERSON_PK" PRIMARY KEY ("PERSON_ID") ENABLE,
    CONSTRAINT "PERSON_PERSON_FK1" FOREIGN KEY ("PARENT_ID") REFERENCES "PERSON" ("PERSON_ID") ENABLE
  );
  /

  create sequence seq_person start with 1 increment by 1;
  /

CREATE OR REPLACE TRIGGER "BI_PERSON" before
  INSERT ON "PERSON" FOR EACH row BEGIN IF inserting THEN IF :NEW."PERSON_ID" IS NULL THEN
  SELECT seq_person.nextval INTO :NEW."PERSON_ID" FROM dual;
END IF;
END IF;
END;
/
ALTER TRIGGER "BI_PERSON" ENABLE;
/

Create an on demand process, to output an Array - which will be later used to draw the chart. I called mine getFamilyData:


declare
  v_out varchar2(4000);

BEGIN
  v_out := v_out || '[';

  FOR i IN (SELECT person_id, name, parent_id FROM person) LOOP
      v_out := v_out || '[';
      v_out := v_out || '{"v":"' || i.person_id || '", "f":"' || i.name || '"},';
      v_out := v_out || '"'||i.parent_id||'",';
      v_out := v_out || '"'||i.name||'"';
      v_out := v_out || '],';
  END LOOP;

  v_out := rtrim(v_out, ',');
  v_out := v_out || ']';
  htp.p(v_out);
END;


Create a form with report on the table you just created.

Create a HTML region - I placed mine below the report, as this seemed as convenient a place as anywhere.

In this region, add the following code (or adapt to suit your needs):


<div id="my_chart_div"></div>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
// <![CDATA[
    
    // Load the Visualization API and the piechart package.   
    google.load('visualization', '1', {packages: ['orgchart']});   
    // Set a callback to run when the Google Visualization API is loaded.   
    google.setOnLoadCallback(drawChart);   
    
    function drawChart() {  
        var data = new google.visualization.DataTable();         
        data.addColumn('string', 'Name');          
        data.addColumn('string', 'parent');         
        data.addColumn('string', 'tooltip');  
        $.post('f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=getFamilyData::::', 
            function(output){    
                data.addRows(JSON.parse(output));   
                var chart = new google.visualization.OrgChart(document.getElementById('my_chart_div'));   
                chart.draw(data);  
            });    
        }
// ]]>
</script>

Basically, you first need a div for which the chart will live.

You need to load the AJAX API, and load the orgchart package so we can draw it in the div. The way all of the charting works is to first create a DataTable object, specify the columns you want/need. Each chart has different specs - as far as column requirements go, so you need to look at what the chart you are using requires - for the org chart, three columns are required (you need not follow the names I gave mine, but they made sense to me):



  • Text - The Name which will appear on the chart node
  • Parent - A unique value which represents a nodes parent
  • Tool Tip - for when you hover over, the text that will be displayed
You can also have each value as an Object where you want the a return value and a display value. I have used this for the first column so if there are two person(s) with the same name, they will definitely be unique from their ID.

So I used AJAX to get the Array data of the person(s) in the database, which calls the on demand process created earlier. It is then necessary to parse it as JSON before passing it into the DataTables addRow function.

Finally the chart is drawn and voila, you have your family tree outputted on the screen.


Side note: Roel Hartman has already created a plugin for this chart type (amongst others). See http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/google-visualizations_29.html.

Tuesday, 8 February 2011

APEX AJAX Basics

Introduction


I'll start of by saying this post isn't about achieving any particular task via AJAX - there are plenty of examples out there already - but how to perform AJAX with Application Express. There seems to be no official documentation on the matter, so wanted to do a general overview on how to achieve AJAX techniques. Of course, it is also worth mentioning, writing your own AJAX functions is not so necessary anymore, with the addition of Dynamic Actions in APEX 4. Never the less, it is still worth knowing.

The most common technique for AJAX in Application Express is to have PL/SQL code in an on demand process, and then calling that process via Javascript at run time. Application Express provides a helper function for calling an on demand process, and that is htmldb_Get (and additionally, apex.ajax.ondemand which is effectively a wrapper object for htmldb_Get object). With the release of APEX 4, came the jQuery library included - so another option for using AJAX is to use the jQuery AJAX API.

htmldb_Get

This function accepts 7 parameters: object, flow, req, page, instance, process, queryString - the most important being the req parameter (3rd one). Detailed explanation:

object – This is the variable that the ouput value of the process will be returned into. It is not important to set this, because the return value of the get function for the object returns this value. i.e. You could either pass a variable into the first parameter, or assign it to the return of the function call.

flow – This is the flowId of the page, which can be accessed via: $v(‘pFlowId’). It is not important to set this, as the object will set the value if it is not set
req – This is probably the most important parameter. This will determine what on demand process will be executed. As you know (or may not) to run an on demand application process, you can do so by placing in the request section of the URL, the text: APPLICATION_PROCESS=processName where processName is the name you gave your application process.
page – Not important as an on demand process isn’t tied to any particular page, however, if it is an authenticated page id, the user must be authenticated, else it has to be a public page.
instance – the htmldb_Get object will again fetch this from the page, so it is not necessary to be set
proc – used for calling a procedure
queryString – to add extra data to the end of the URL

Most of the attributes explanations were just paraphrased from Carls sample application, so please see that for a more detailed explanation – http://htmldb.oracle.com/pls/otn/f?p=11933:63

Most of the parameters are irrelevant, as default values are populated in the htmldb_Get object. So all you really need to call when running an on demand process is:


var get = new htmldb_Get(null, null, 'APPLICATION_PROCESS=processName');
//add some parameters to use in your on demand process
//session
get.add('SESSION_ITEM','SESSION_VALUE');
//or temporary
get.addParam('x01', 'x01_value');
var gReturn = get.get();

Any output generated (with htp.p or htp.prn) in the on demand process will be returned from the get() function of the htmldb_Get object. In the above snippet, returned into the variable gReturn.

Since it is an on demand process, which is PL/SQL, you can use regular session state variables just like in any other PL/SQL process. However, more than likely, you want to pass some value that does not yet have some submitted value. So, you can use one of the available functions of the htmldb_Get object:
  • addParam – Where the 1st parameter is a temporary item that you want to set the value of, and the 2nd parameter is the value of said item. There are 10 temporary items that you are able to set, and they are x01 to x10. Then in your PL/SQL process, you can refer to this item with: apex_application.g_x01 (assuming you set the parameter x01).
  • add – Where the 1st parameter is the session item that you want to set the value of, and the 2nd parameter is the value of said item. I personally think it better to create a temporary application item for use in your on demand process as it will make your code that much more readable. But whatever pleases you :-). Then you just refer to this item as per usual. i.e. bind syntax or with the v/nv functions.
There is also another object you can use, which is a wrapper for htmldb_Get – apex.ajax.ondemand – where you just need to pass in the name of the on demand process. i.e:

var aReq = new apex.ajax.ondemand('processName', function(){ //todo: success. 
 });
aReq._get();



Where the 2nd parameter is the return function, which you can program your function to behave based on the XmlHttpRequest objects’ ready state – which is referred to with the variable p. i.e. if (p.readyState == 4) { var response = p.responseText; }


jQuery

Since APEX 4 comes bundled with jQuery now, another possibility is to make use of the jQuery AJAX API that is available to you. See: http://api.jquery.com/category/ajax/

I tend to just use the jQuery post function, as it effectively wraps up the ajax function setting the properties used for a POST request.

As previously mentioned, when you specify ‘APPLICATION_PROCESS=processName’ in the request portion of the URL, that will result in that on demand process being executed. With that in mind, there are two URLs that you can post to.



  • The application URL with the request portion set accordingly
  • Post to wwv_flow.show and set the data for: p_request, p_instance (the session), p_flow_id (application number), p_flow_step_id (page number) and any session items you want to set the value of, with a combination of p_arg_names (session item) and p_arg_values (item value)
Passing parameters is a little more complicated using jQuery, as there are no add functions to specify a session item to set the value of. If using the standard URL for your request, you can just set session items as per normal, using the standard URL syntax. If however you want to set session items using the wwv_flow.show URL, you need to add a parameter of p_arg_names, which represents the session item, and p_arg_values, which represents the session value. For multiple items, you would make sure they are added in the correct order so that argument names and values are not mixed up. You can also add the temporary items (x01-x10) as per normal, i.e just pass them as an additional parameter.

Examples

Pass the standard URL to run the on demand process getTest, setting a session state item TEST_ITEM, alerting of the returned output:

$.post("f?p=106:2:3500881246167611:APPLICATION_PROCESS=getTest:::TEST_ITEM:test_input", function(data){alert(data);});


Pass the wwv_flow.show URL to run the on demand process getTest, setting a session state item TEST_ITEM, alerting of the returned output

$.post('wwv_flow.show', 
  {"p_request" : "APPLICATION_PROCESS=getTest", 
  "p_flow_id" :  $v('pFlowId'), 
  "p_flow_step_id" : $v('pFlowStepId'), 
  "p_instance": $v('pInstance'), 
  "p_arg_names" : "TEST_ITEM", 
  "p_arg_values" : "test_input"}, 
  function success(data) { 
   alert(data); 
  });


Pass the wwv_flow.show URL to run the on demand process getTest, setting a session state item TEST_ITEM, and setting the temporary item x01, alerting of the returned output:

$.post('wwv_flow.show', 
  {"p_request" : "APPLICATION_PROCESS=insertDum", 
  "p_flow_id" :  $v('pFlowId'), 
  "p_flow_step_id" : $v('pFlowStepId'), 
  "p_instance": $v('pInstance'), 
  "p_arg_names" : "TEST_ITEM", 
  "p_arg_values" : "test_input", 
  "x01" : "x01_value"}, 
  function success(data) { 
   alert(data); 
  });

Useful Links