*Updated* Apex jQuery spinbox v1.0 [plugin]

After a review of Patrick Wolf, a few changes are incorporated to make this plugin a 1.0 version. As I said before, my first try was really simple (hence the 0.1), and Patrick took the time to review and change/add some of the code of the plugin. The following things have changed:

* Minimum value is required
* New number alignment attribute
* Autocomplete for text field is turned off
* New validation code that:

1. Checks if the value is numeric
2. Checks if the value is in range of min/max
3. Returns validation messages

* Renamed the library files according to apex jQuery namespace
* Added source files in release
* Added server ready files in case of webserver install
* Added a readme file
* Added installation and de-installation steps in the readme file

You can still find the demo on this location.
The download is stille hosted here.

And again, thanks to Patrick for the help!
Now go on, have fun with this plugin!

Apex jQuery spinbox [plugin]

!!===========================================================
New version available check the next post.
!!===========================================================
This is the first plugin I created, using the spinbox plugin for jQuery, and wrapping some PL/SQL around it. It's very comparable to Patrick Wolf's mask example.

It's a very simple plugin, only rendering the field with extra up and down buttons at the end of the field, by clicking it adds or subtracts the "stepsize" parameter set in the settings. You can also set minimum and maximum parameters if you want to restrict the numbers (client side only ofcourse).

The demo: http://apex.oracle.com/pls/apex/f?p=44781:3
The download: http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/spinbox.html

Have fun with it.

Plugging in the Apex Item Plugin

Is it really more than a year since my last post on this blog? Time flies when you're having fun. Talking about fun, you probably noticed the release of Apex 4. This action packed release has a lot to offer. I just recently took the time to do some research on the new features. And yes, most like everyone else in the Apex blogging scene, I really wanted to try some plugins and dynamic actions.
Funny observation: The most mentioned feature of Apex 4 before the Early Adopters editions, websheets are not mentioned as often anymore.

Having said that, today I wanted to write about my view on the apex item plugins. I've created one, and I must say it is really nice functionality. It'll create a lot more flexibility by adding almost limitless options. If this is a good thing? More on that later.

Item plugins in apex...gamechanger?
Many item plugins are actually done many times before. Think of one really original item type or functionality in an item, and it'll probably been done before. So the item plugins feel more like "closing the gap" with other frameworks that allready have rich item types, or plugin functionality (Like ADF and .NET), than a game changer in framework choice.

Closing a gap? ....how fast?
Another very cool thing to bear in mind, is that Apex almost seamlessly integrates jQuery in it's pages. jQuery has supported plugins for their framework for some time now, and there are lot of plugins lying around.
With item plugins in Apex it's almost logical to assume that Apex developers don't want to double their work. So get a jQuery plugin, wrap an Apex plugin around it, and boom, there you go... working jquery plugins as declaritive items in apex. So it's pretty easy to create a lot of item plugins for apex on the short term. I think that in a year or so, almost all mainstream item types found in other frameworks, can be done in Apex. Different apex plugin repositories are allready popping into existence, hoping to be the next App Store for Apex Developers.

Things to think about
While the general concensus will probably be positive on item plugins, there are still things to think about. One of the key aspects in Apex developement for me, was the fast, uniform way of working. No matter what client, or what project, the tool and it's functionality was the same. For me this is/was one of the key aspects in the debate on Java and Apex, mentioning JAF (Just Another Framework) very often to the Java guy. I'm well aware that the plugins for Apex are nowhere near as complex as a completely different framework in Java, but I'm just saying that spiderman's Uncle Ben had it wrong, he should've said: "With great flexibility, comes great responsibility". Ironically, you now have more potential to lock your environment in flexibility, because it has so many different little plugins.

So... do I like it ?
Yes! I like the plugin system very much! I think it's great, and I see a big maturement of the framework. This will really help arguing on why Apex is a good choice in many situations. But it's the architects/developers responsibility to restrict some of this newly found flexibility. Define, Create, Copy, Discover or just write a basic set of plugins for your Apex environment. Work with these plugins across the line. Really think about why you would use a plugin, and if so, incorporate it in the environments plugin repository. Don't let it get messy, it'll break you up one day.

Later today, I'll post my first (very simple) Item Plugin.

P.S.
All of the above is quite inherent with any modular software developement....

The Apex ACP bèta is here!

If you want to convert your Anychart 3 charts to Anychart 4.2.1, I have some great news! You can do it now, for free! Check these demo's.

Today I added the download link on the Apex Anychart Conversion Package website.
This means the first (bèta) release is public. And you're all invited to download and try the Apex ACP.

If you want to install the package, please have a look at my installation guide and video here: Installation Guide
I've also added the installation video to this post at the bottom.

If you find any bugs, you're more then welcome to share your problems, right here: Report a bug


Well, I hope everyone will have little problems installing the package. And I hope we don't have to use those Anychart 3 charts anymore.

I'm very interested in your opinion, so throw me a line if you want in the comments !

The Apex Anychart Conversion Package (ACP) featurelist

Status,

I've nearly finished the first version of the Apex ACP, the first conversion package for Oracle Apex to upgrade your existing charts to Anychart 4 while still having the power of the Apex builder wizards and chart configurations. And the best thing is, it is absolutely free!

Anychart 4.2.1 is allready supplied in the standard version of Oracle Apex, so you're making the best use out of the available resources.

Site,

I've created this site: http://www.apexacp.blogspot.com

On this site you can view the features and known limitations of the ACP. When the package becomes available (or, when I finally get the time to make an installation script ;) ) it will be available here to. For now, you can view the features and the video demo's I've created.

Plugin,

Inspired by Patrick Wolf's Apex Builder Plugin, I've create an Apex ACP Plugin for the greaseMonkey firefox plugin. With this plugin you'll be able to convert your chart with the click of a button.

You can also find a new demo on the Apex ACP site !

Features,

As listed on the Apex ACP site, here is a summary of the Apex ACP features:

Version 0.6 (first bèta release)

Features:

1. Better export functionality (supported by Anychart 4.2.1):
- Save chart to PDF
- Save chart as image
- Print chart directly
2. Better look and feel creating a better overview
3. More grid lines on the chart
4. The use of all standard APEX wizards in the Apex Builder
5. Easy conversion plugin changing your chart with the click of a button
- This uses greasemonkey
6. Convert the following charts from Anychart 3.3 to Anychart 4.2.1 :
- Candlestick
- Horizontal Range 2D Column
- Horizontal Range 3D Column
- Horizontal 2D Column
- Horizontal 3D Column
- Range 2D Column
- Range 3D Column
- Scatter
- Stacked Horizontal 2D Column
- Stacked Horizontal 2D Column (Percent)
- Stacked Horizontal 3D Column
- Stacked Horizontal 3D Column (Percent)
- Stacked 2D Column
- Stacked 2D Column (Percent)
- Stacked 3D Column
- Stacked 3D Column (Percent)
- 2D Column
- 2D Doughnut
- 2D Line
- 2D Pie
- 3D Column

See the site for more details.

Sneak Preview #2 - Apex ACP (Anychart Conversion Package)

The Apex ACP converts your "old" Anychart 3.3 flash chart to the fancy "new" Anychart 4.2.1 chart.
Here's another sneak peak at the results you can achieve with the Apex ACP. This demo shows the conversion of the sample application chart on page 5. It also shows that you can use (standard) FlashChart queries, just as you're used to do with the other charts. It also shows a different chart based on the type of chart you select. So you can see that the position and 2D/3D settings are created based on the type of chart you select.



The Apex ACP will probably be available somewhere next week, in a pre-release bèta version. If you're interested in trying the package out, you can email me on rutger.deruiter@gmail.com.
All features and limitations will be listed early next week!

Sneak peak - APEX AnyChart 4 Conversion Package

Hi all,

I just wanted to show a sneak peak of the results with the Conversion Package. I know the APEX dev team has stated in their statement of direction, that Anychart 4 will be integrated into APEX 4.0, but there are some good reason to do it anyway.

1. I want it now ;)
2. Customers are really asking for this!
3. You never know, maybe it'll be postponed to version 4.1 (statements of direction are just guidelines, right? :) )

Just a little heads-up, the video is not as high a quality I'd like. So view in Full Screen and ignore the smudgy looks.




Before:


After:



The planning for now, is to support all of APEX's default Chart functionality. So more on this subject soon!

Greetings,
Rutger

Pro4Pro APEX session 28-10-2008

Next Tuesday I'll be hosting a real nice session at the Logica office in Arnhem in the Netherlands. Together with colleague Roel Hartman, we'll be building a web application in a little over an hour. The power of this session is that you'll see everything we're doing to make it work, while also providing background information about the moving part we're handling.
After this part there will be time for a real cool part of the session, "Things you'd like to see". In this bit the audience can just ask away, and if it's possible we'll create some elements on the fly.
The subjects that will be covered in the session:

- Reporting and data entry
- Security
- Personalizing an application
- Things you'd like to see
- The fully fledged version, or: "What we've build in just three days"

So if you are in the neighborhood you're very welcome to come, as this session is open to anyone. The presentation will be in dutch, and you can apply here: http://www.logica.nl/pro4pro

The session starts at 18.30, and dinner is served at 17.00. (It's required to apply when you want to attend)
The address and route descriptions to the Logica Arnhem office is provided when you apply.

Hope to see you there!

Update multiple items from a LOV field with JSON

Ever wondered how great it would would be, if you could change or fill multiple item fields from a selected LOV.
Some people did, and came up with javascript solutions filtering out their own return strings, usually separated by a magic separator character (like ~).
In modern web design we'd actually want something where we can just fire away and retrieve some data, and be able to quickly (magically) retrieve separated data. Here's where JSON comes in. If you haven't heard of it, a lot of blog posts have been written about it as of late.
But in a "quicky", this is what it is: JavaScript Oject Notation (JSON) makes sure you can wrap and unwrap data in javascript. The power of JSON is that it is light weight. You are just passing strings of text. The format is making sure the right data is given the right name. In the calling javascript bit, you get an array that you can access. This array is created in an object notation. For more info on JSON itself, visit the site here.

=== Footnote ===

---

APEX is incorporating JSON in it's inner workings, even creating JSON calls in the APEX API. Carl Backstrom is really getting it together with the JSON calls, making communication between the APEX page and the database even easier. The known API calls are:
- APEX_UTIL.JSON_FROM_SQL (Returns a general JSON formatted text)
- APEX_UTIL.JSON_FROM_ITEMS (Returns a JSON string for "json_SetItems" command)

Both will be demonstrated in this post!

Getting it to work with a LOV field

JSON_FROM_SQL

Getting it working is fairly simple, we need javascript to get some event action going, and an application process to handle the API calls to the APEX_UTIL package. Since we want to do things dynamically, without any page loads, we'll use AJAX calls to trigger the application process code.

First off, I created a form on a table (I'll be using the DEPT table in this example). I changed the DNAME field to a Popup LOV, that selects all department names. (This could be a select list as well).

The second step is to create a callable javascript function that will make the AJAX call to the application process, parse our JSON text to a JSON object, and set the values to the items. Create something like this:

Now before you're going like, what is that JSON.js script doing there at the beginning. This is a JSON support script that gives some extra functions to help us making our lives easier (like parseJSON, instead of the eval function *See footnote for more info). You can ofcourse just download the script and upload it to your own web server, but this is easier for demonstration purposes.

The script is commented, so if there are any code questions, be sure to respond in this post comments.

The third step, is to create the application process that is called from the AJAX call in the javascript. The call looked like this:

ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=DEPTLOOKUP',0);

So we need to create a Application Process called DEPTLOOKUP, looking like this:

Edit 13-10-2008 - Important if you are using this!
Thanks to Patrick Wolf, who noticed the SQL Injection vulnerability in the previous code. Be sure to use bind variables where you can, and if you can't use the DBMS_ASSERT package to filter out malicious characters.

DECLARE
 
-- We set wwv_flow.g_x01 in the javascript part
-- you can use this parameter as a temp APP ITEM

-- EDIT 13-10-2008: Thanks to Patrick Wolf, for noticing the SQL Injection threat
--                  Added DBMS_ASSERT.SIMPLE_SQL to check for weird user input
--                  And the ENQUOTE_LITERAL to prevent hardcoded quotations
p_dname varchar2(20) := DBMS_ASSERT.ENQUOTE_LITERAL(DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
 
BEGIN

--Use this to get a JSON string back in the javascript, based on the SQL inserted

APEX_UTIL.JSON_FROM_SQL('SELECT deptno, loc FROM dept WHERE dname = '||p_dname);
 
END;

As you can see, the parameter is picked up, and used in the JSON_FROM_SQL command. This command will automagically return a correct JSON string to it's caller. In this case the javascript.



The fourth and final step, is to trigger the javascript. For the purpose of the LOV, an "onchange" event does the trick perfectly. If you select another item from the LOV, the onchange event is triggered. So go to the details of the LOV item, and add to the "HTML Form Element Attributes" of the "Element" section:

onchange="lookupDeptno();"

If you did the above, you should get behaviour like this:
http://apex.oracle.com/pls/otn/f?p=33575:1

=== Footnote ===

---

The parseJSON function is the reason to incorporate the JSON.js "libary". The alternative of using the parseJSON function, is to use the javascript "eval" function. This function shares most of it's letters with another word that can describe this function: "evil"!!! Eval executes anything that is passed into it. So it's a potential security risk to use eval.

JSON_FROM_ITEM
This approach is similar to the previous one, but with one fundamental difference. With the JSON_FROM_ITEM call you set the items values from the APP process instead of the page javascript.

The first step is to have a popup LOV item again, that fires some javascript code. So create a form on a table (I used the DEPT example again).

Second is to create the javascript that is triggered, that will make an AJAX call and refresh the values.
Here's the code:

The third step is creating the application process "DEPTLOOKUP_ITEM". This app process will get the table record, set the new values into the session state and create a JSON string that is returned for the "json_SetItems" javascript call. And it looks like this:

declare
 p_dname  varchar2(200);
begin

 p_dname := DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
 FOR c_dept IN (select * from dept WHERE DNAME = p_dname) loop
  APEX_UTIL.SET_SESSION_STATE('P4_DEPTNO',c_dept.DEPTNO);
  apex_util.set_session_state('P4_LOC',c_dept.LOC);
 end loop;
 apex_util.json_from_items('P4_DEPTNO:P4_LOC');

end;

Finally, you need to create the onchange event again, in the "HTML Form Element attributes" to:

  onchange="getItemsFromJSON();"

This way you're able to change the values of the items automagically as well.
See the results here:
http://apex.oracle.com/pls/otn/f?p=33575:4

=== Footnote ===

---

Be sure to code the "ajaxRequest.get()" and the "json_SetItems" real close to each other, and make sure that there are no errors "in between" these lines of code. If there is an error generated between the "setting of session state" and updating it on the screen, you will get unexpected behavior. The session state has changed, and you have no idea that it did.

Conclusion

Using these JSON techniques really adds up to your page's usability. It can be an extra argument for customers that are considering APEX and are using Oracle Forms. A lot of Oracle Forms use this "LOV to fill multiple items" functionality. Usually with WHEN-VALIDATE-ITEM or POST-CHANGE triggers.

Credits must go out to Carl Backstrom, for integrating JSON with APEX, and also the examples he put forward (which are very similar to mine), so you should really take a look at his blog!

*PS
If this was helpful to you, your very welcome to share this in the post comments ;)

Using anycharts 4 with APEX 3.1

If you haven't noticed before, Interactive Reports use Anycharts 4.2.1 ! While the normal flash chart regions are still using Anycharts 3.3!

The 4.2.1 version has much cooler features, as saving to image or to pdf with the right click menu. It also looks better and more organized. Why are normal flash chart regions still using 3.3? Well, probably because the XML source definition has changed! With APEX release 4.0 I think we can use Anycharts 4 with normal regions (Could be 3.2 but I think they have their hands full with Forms2Apex)

So what are we to do? While waiting is an option, trying to hack into Anycharts 4 is way much more fun! First some background info!

Read More!

Anycharts 3 & APEX architecture
Anycharts3 is using an .swf file for almost every different type of chart (and can be found in the /i/flashcharts directory). For every chart an .xml file has to be provided with the specific layout and data properties. Here's where APEX kicks in, the xml definition is generated by a procedure that is called APEX_UTIL.flash (Who's in charge off naming things over there at the APEX Dev Team?). There are some parameters provided, like the APP_ID, PAGE_ID, SESSION etc. More important is this parameter: FLOW_FLASH_CHART_R#REGION_ID#. And it's more important because it provides the apex_util.flash procedure with the region ID where you defined all your settings in a Charts Region (Like labels an colors). This ID is probably used in the procedure to get these values, and use them to generate a xml definition.

The resulting xml from apex_util.flash is parsed on a HTTP request! Just like any other page, the chart just shows the xml definition as a text/xml content type in your browser if you put the reference in the URL. This reference (with absolute path) looks like this: http://10.0.0.1:8080/pls/apex/apex_util.flash?p=105:8:985557025495156:FLOW_FLASH_CHART_R5188815604017627_en-us
In anycharts 3 the path is added as a parameter called XMLFile (remember that one) and is passed to the swf flash file.


Anycharts 4 & APEX Architecture
In Anycharts 4, all different types of charts are collected in one .swf file. This is again, combined with an xml definition file, telling how to layout the chart and what data is in there. The anycharts 4 xml files are different from the anycharts 3 xml files, because it has a different buildup. The anycharts 4 xml file is also generated from APEX! The procedure taking care of this is APEX_UTIL.flash2 (Honestly, who's in charge of naming, common!! ;) ). It is also called from the page object, but this time it is different. It is passed in the "flashvars" parameter as "XMLFile=http://10.0.0.1:8080/pls/apex/apex_util.flash2?p=etc. etc"
And this time, the XML defines what type of chart it is ! Not the flash file! So this adds great flexibility to developers!


Using anycharts 4
There are several ways to use anycharts 4. Here's what I was able to do:

1. Create anycharts 4 from anycharts 3 region (but has some drawbacks)
2. Reference an anychart 4 chart from an interactive report
3. Create your own definition and put it in the images directory
4. Create your own definition and reference it trough a procedure (just like APEX_UTIL.flash2)

1. Create anycharts 4 from anycharts 3 region
Why not change the APEX_UTIL.flash procedure to APEX_UTIL.flash2? That's also the first thing I was thinking. But you have to make some more adjustments.

• Create a chart region with the APEX wizards
  
• Just create a normal HTML region
• Check out the code of the chart region at runtime (with Firebug offcourse)
  
• Copy the code between the OBJECT tags (include the
• Cut the swf variables ?XMLFile=etc. to the Flashvars param (and remove the "?")
• Change the APEX_UTIL.flash to APEX_UTIL.flash2

This tries to give you the definition of the Anychart 4 xml, interpreted by the anychart 3 settings you did in the charts region. Major drawbacks of this approach is that you cannot define the type of chart. It just gets the first one, wich is "CategorizedVertical". But colors and labels are added to the chart! Also, you have to have the anycharts 3 region in your page for this to work, but you can hide it if you want!


2. Reference an anychart 4 chart from an interactive report
If you create a chart in an interactive report, you can reference this in a HTML region. Changes made in the IR are also changed in the chart. If you want a dynamic anycharts 4 chart, this is a great way to do it!

• Create an interactive report
• Create a chart in the IR
• Save it as default!
• Copy the code below




• Paste it into a HTML region
• Change the parameters in the XMLFile parameter (page 6 is where the IR was, so change that to the page that your IR is)
  

The only drawback of this approach is that you don't have direct control over the layout of the chart. (like borders and backgrounds)


3. Create your own definition and put it in the images directory
You can also just skip the APEX generation bit, and create your own XML file. You just have to reference it in the code for the swf file.

• Just create an XML file with for example the following code:

	
		
			
				
					
						
							
2601
						
					
					
						
							
2739
						
					
					
						
							
2790
						
					
					
						
							
2908
						
					
				
				
					
						
							
2925
						
					
					
						
							
2465
						
					
					
						
							
2231
						
					
					
						
							
2426
						
					
				
				
					
						
							
2231
						
					
					
						
							
2101
						
					
					
						
							
2084
						
					
					
						
							
2685
						
					
				
			
		
			
			
			
			
			
			
				
					
				
				
					
						
					
				
			
		
			
				
					
{%Name}{enabled:False}  {%FValue}{enabled:False}
				
			
		
		
	

• Next put the file in your /i/ directory
• Just copy the previous OBJECT code
  
• Reference your xml file in the XMLFile parameter as /i/yourxml.xml instead of the APEX_UTIL.flash2 string

4. Create your own definition and reference it trough a procedure
Now for the really interesting one! And this requires some additional settings. But what if you could just work the same way APEX works, with a packaged procedure, referenced in the URL (APEX_UTIL.flash2). But now we want to define our own procedure, which parses AnyChart 4 XML instead of 3, but now we can control it ourself! (Unlike flash2).

• Create a package (like "anycharts_xml_generator")
• Create a procedure in this packaged with a parameter XMLCallData number(like "procedure simple_xml(XMLCallDate IN NUMBER DEFAULT NULL)")

*XMLCallDate is a parameter that is added at runtime, probably by Anycharts. It is important to add it ! Otherwise you will get an "IO Error" message, and it takes a while to find the problem!

• Put in the following body

procedure p_simple_xml(p_param IN number default null, XMLCallDate IN number default null) IS

BEGIN



owa_util.mime_header('text/xml', FALSE, 'utf-8');

owa_util.http_header_close;

htp.p('');



htp.p('







 

   

   

   

   

   

 





 

 

   

     

   

   

     

     

   

 


















');
END;

• Next you need to make this procedure callable, so go to the FLOWS030100 schema
• Go to the Function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL
• Add the "ANYCHART_XML_GENERATOR.SIMPLE_XML" to the following piece of code:

if upper(procedure_name) in (

   'ADD_AS_A_FRIEND', 'ANYCHART_XML_GENERATOR.P_SIMPLE_XML') then

 return TRUE;

else

 return FALSE;

end if;

• The above code makes sure that you can call the procedure from the URL
• Next we need to change the reference for the XMLFile in the HTML region.
  
• Change the XMLFile parameter to:

  http://yourIP:port/pls/apex/anychart_xml_generator.simple_xml?p_param=void

* Thanks to Mario Jozak, who pointed out to me that there HAS to be a parameter present, because the XMLCallDate is pasted as "&XMLCallDate" to the call. If this was the only parameter you will get an IO Error because the call would look like "p_simple_xml?&XMLCallDate" which is wrong! So thanks again, to Mario.
• Run the page!

Final thoughts
As you can see, Anycharts 4 is a huge improvement over 3.0, not only on the visual side, but also with the functionality and flexibility it gives.
With the 4th way to get AnyCharts 4, it is is possible to create a Anycharts 4 generator, just like APEX_UTIL.flash2. So if some of the charts just aren't good enough for you, you can create them yourself!

Let me know, if you start using Anycharts 4, as I'm currently checking if I can create a generator!