Thursday, October 23, 2008

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:

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!

Monday, October 6, 2008

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


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.


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


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


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:


If you did the above, you should get behaviour like this:

=== 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.

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:

p_dname varchar2(200);

p_dname := DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
FOR c_dept IN (select * from dept WHERE DNAME = p_dname) loop
end loop;


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


This way you're able to change the values of the items automagically as well.
See the results here:

=== 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.


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!

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

Wednesday, September 17, 2008

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!

Friday, September 5, 2008

AE Training days Utrecht

I attended the Apex Evangelists training days this week in Utrecht. From tuesday till friday I listened to John and Dimitri, who were presenting a variation of topics.

First off, let me thank Dimitri and John for the great organization! Unfortunately I couldn't attend the bowling trip, not that I would have made any difference with the bowling itself (I kinda suck at it ;) )

The best thing with this training was the extent of topics covered, for starters and more advanced developers. I know from first hand experience it's very hard to create an allround presentation about a topic.
It also shows the expertise and flexibility that John and Dimitri have.

I especially liked the presentations on the APEX Dictionary, Best practices, the security one and the 3d party integration! I really got some new things I'm gonna look in to or use!

Oh...and if some of my future blog posts contain a topic that looks like a topic on the training I'll certainly reference the training :)

Finally I can really recommend this training to anyone who wants to know more in depth information on APEX.

From a very reliable source, I heard John's book will be finished before Open World 2008 ;) Sorry John, but you'll not get the question anymore after that :)

Monday, July 7, 2008

Drag&Drop Planboard

Creating out of the box functionality for web apps with APEX is as fun as it is challenging. Some time ago Roel Hartman and me started working on a transport and logistics application in APEX. (Roel allready blogged about it once here)
One specific part of this application was a planboard which used dragging and dropping with orders in sort of a truck calendar. It was build in Java and had all the swingy features you'd expect.
The client wanted to know if this was possible with APEX. Well, sure it is. We'll just use the full pallete available for us with the web browser! CLICK HERE TO SEE IT.
Obviously their are many paths to take when considering the design for this sort of functionality, especially when you have APEX at your disposal to create it.

Should we use the available calendar?
Could we use a tabular form?
Do we use standard APEX DML report/forms?

Well, this is what we did ;)

The design

1. Collect the data for the planboard view, it will show the truck number (equipment number) and the orders in the days of the week:



SELECT trucks.truck_id
, to_char(to_date(load_date), 'YYYYIW') load_week
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'maandag' , order_id, null )) mon
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'dinsdag' , order_id, null )) tue
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'woensdag' , order_id, null )) wed
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'donderdag', order_id, null )) thu
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'vrijdag' , order_id, null )) fri
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zaterdag' , order_id, null )) sat
, max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zondag' , order_id, null )) sun
FROM trucks,
WHERE orders.truck_id (+) = trucks.truck_id
GROUP BY trucks.truck_id,
to_char(to_date(load_date), 'YYYYIW')
ORDER BY trucks.truck_id
2. The report region has the planboard view as source. For every column box (value or not) a unique id is created . These id’s make sure that a column box is a container where you can drop items in. The column boxes with a value, should show the item with its value.

Report buildup

The column formatting for every day column

3. The report region must also call a javascript function CreateDragContainer, for every container in the region. While doing this, all items inside a container (or just the values selected with the view) become draggable. With the unique id’s, movement can be tracked, and when an order item is dropped in a container, the rest of the handling can start. This is all done by Javascript, where credit must go out for a part of the dragging & dropping to Mark Kahn at webreference.
The most important part is that the id's are filtered, and passed to application items, and that an AJAX call is made to an application process. Here's the AJAX call code:

var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=updatePlanbord',null);
if (,3) == 'new'){
ajaxRequest.add('P_ADDACTION', 'true');
var cDay =,12);
ajaxRequest.add('P_ADDACTION', 'false');

var cDay =,8);
var cDayDate
if (cDay == 'mon'){
cDayDate = html_GetElement('P1_MON_DAT').value;
}else if (cDay == 'tue'){
cDayDate = html_GetElement('P1_TUE_DATE').value;
}else if (cDay == 'wed'){

cDayDate = html_GetElement('P1_WED_DATE').value;
}else if (cDay == 'thu'){
cDayDate = html_GetElement('P1_THU_DATE').value;
}else if (cDay == 'fri'){
cDayDate = html_GetElement('P1_FRI_DATE').value;
}else if (cDay == 'sat'){
cDayDate = html_GetElement('P1_SAT_DATE').value;
}else if (cDay == 'sun'){
cDayDate = html_GetElement('P1_SUN_DATE').value;

ajaxRequest.add('P_LOADDATE', cDayDate);


4. The application process can do two things, UPDATE an existing order when it is dropped in another truck, or UPDATE an existing order when it had no truck (open action) and was dropped in another truck. Here's the code:

type_action varchar2(10);

type_action := ''||:P_ADDACTION||'';

IF type_action= 'false' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET AND to_date(load_date, 'dd-mm-yyyy') = to_date(''||:P_LOADDATE||'', 'dd-mm-yyyy');

ELSIF type_action = 'true' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET;



After some CSS tricks and custom theming, you get the result you see in the browser. Needless to say, the client was quite impressed with the result, and I think it is another example of the flexibility APEX has to offer being in a web browser!

If you'd like to know more on this application and you're at the Oracle Open World this year, vote for this presentation on Oracle Mix by clicking HERE

Friday, June 27, 2008

APEX rocks --> APEX workshop @ Logica Arnhem

Well, yesterday I hosted my first APEX workshop from a teaching perspective, and I must say it is a whole different experience then being the attendee. Luckily Roel Hartman (we hosted the workshop together) and I had prepared the workshop properly.

First off, I gave a presentation about APEX. My focus was especially set on "seeing the point" about what APEX is, does and what it is intended to do. This combined with a healthy summary of features and architectures made the presentation quite long (about 50 min) but it was the minimal version, because I wanted to put so much fun stuff in that APEX has to offer. I just couldn't decide what to scrap!
I must say, the presentation went as expected, and the reaction afterwards were very positive, so thanks for the compliments.

Next up, was the workshop. We created a beginners workshop, for people who had never before worked with the magic of APEX. There was little or no experience with HTML_DB or APEX in the room among the attendees, so I was a bit nervous about the complexity of the workshop.

Apparently I forgot how easy it was to start working with APEX, because people were flying through the workshop! Luckily, somewhere in the middle they reached some more complex material, which provided the attendees with some more challenges.
The feedback we received on the workshop was again, very positive.

Finally, after some quick&clean application building, Roel gave his presentation about the ODTUG'08 Kaleidoscope where he attended earlier this month. A lot of cool new features / directions came to light, creating a bit of a buzz followed by a good discussion among the attendees.

Yes it was happening, the healthy addiction of APEX found some more hosts once more!!

This workshop created a lot of enthousiasm among the attendees, and I think almost everyone wanted a follow up workshop with more details. This is (again) a clear case of how low the threshold is that APEX has when people are just starting to create some application with it.

In case you haven't noticed yet, APEX rocks!