Monday, 10 December 2007

UKOUG - Day 3

So with my presentation firmly behind me, and after having the best night's sleep in weeks, I headed into day 3 looking forward to catching John Scott talk about 'Debugging APEX Applications', Dimitri Gielis present 'Integration of BI (XML) Publisher and APEX (Oracle Application Express)' and also sit on the panel for my first APEX roundtable discussion group.

John's presetation was very good, he presented well and showed numerous techniques for debugging your APEX applications. Specifically of interest was the use of the DBMS_APPLICATION_INFO package which can be used to monitor progress of long running queries / reports and display this progress information back to the user. He also mentioned that as an APEX developer he believes that you have the responsibility to have a good understanding of the database, so as to be able to benefit from the features and build better applications, a point which I totally agree with. If you ever get a chance to watch John present, then do it as I'm positive there will be something in there that you can benefit from. Nice job John!

So then it was time to get ready for the APEX roundtable. I was pretty nervous and left John's presentation quarter of an hour before the end so as to go up to the room and get myself ready. The session was chaired by Jeremy Duggan (Chair of the Modelling, Analysis and Design SIG), and I was on the panel with Dimitri Gielis, Peter Lorenzen and unofficially, but answering a lot of the questions John Scott. Around 20 people turned up, which was reasonable considering it was lunch time! Some of the topics that came up were:

  • APEX / Forms / ADF
  • Choosing the right tool for the right project. This is a massive topic with lots of arguments for and against. For a good overview of the main factors, take a look at Duncan Mills' article, 'The Right Tool For the Right Job'. Also, if you are in the Netherlands, Dimitri will be discussing this with Lucas Jellema on Monday 17th December.
  • Validations
  • Specifically around the current issues with validating tabular form data. Dimitri mentioned Patrick Wolf's ApexLib framework which greatly improves tabular form handling, including out-of-the-box client and server validations for mandatory, date and numeric fields. Alternatively, if you don't want to or cannot use Patrick's framework, you can code your validations referencing the global fxx arrays as mentioned in this rather old, but still useful article. Finally, the Statement of Direction implies that version 4.0 will give us, 'Improved tabular forms, including support for validations...', so looking forward to that.
  • URL Tampering
  • And how this can be combated using the APEX built-in 'Session State Protection', see Dimitri's article or the official documentation.
  • Page Comments
  • Specifically, can these be mandated if this is a development standard? No, not currently but as John suggested this could be easily monitored through the APEX views. The issue also came up that the comments are right down the bottom of the page, which can lead to oversight. This question came up in the forums a couple of weeks ago and Patrick Wolf added a feature to the APEX Builder Plugin which addresses this issue by highlighting the 'Comments' link in yellow if there is a comment. Thank you Patrick!
  • Source Control
  • This keeps on cropping up at the moment. Basically if you are using a source control system such as CVS or SubVersion, what is the best strategy for managing the APEX application files? This can either be done at page level or application level and can be automated through use of the supplied command line tools 'ApexExport' and 'ApexExportSplitter'. APEX development team, how do you manage this?
  • JavaScript
  • A few JavaScript related questions came up. There are lots of libraries available in the APEX release that you can make use as a developer, the functions are unofficially documented by Carl Backstrom and according to the statement of direction will be officially documented and supported from 3.1, which is good news. Also what happens if a user has JavaScript turned off? Well, APEX can be used to build applications that meet accessibility requirements, but it requires some workarounds. See Sergio's article Application Express and Accessibility if you are interested in the steps involved. Peter also added that all client-side validations should be backup up with server-side / APEX validations as best practice.
  • Team Development
  • What is best practice for working on large projects with many developers? We mentioned an excellent article written by Ben Wootton, entitled, 'Best Practices for Oracle Application Express Collaborative Development' as an excellent reference point. This details use of page locks, page groups, use of Application Reports for monitoring, commenting changes, use of PL/SQL functions / procedures rather than embedding logic in page processes and lots more.

Dimitri and John after the session.

So that was about it, a very interesting and interactive discussion with loads of input from all the panel and much of the group, Jeremy doing a good job of keeping it all together. And it was great to meet Dimitri and Peter for the first time and catch up with John having met him on day 2.

Unfortunately I had to head back to Reading earlier than expected in the afternoon, so was unable to catch Dimitri's presentation. A very enjoyable day none the less and looking forward to next time!

UKOUG - Day 2

It had finally arrived, Tuesday 4th December. A day that had been engrained on my brain ever since receiving 'UKOUG 2007 - Speaker Confirmation' in my Inbox back in July. The day of my first big presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax'.

So very nervous I headed down to Birmingham from Reading and arrived at the ICC at around 9.30am. I was speaking at 11.15 so gave myself a bit of time to register and go over the slides one last time. A worthwhile activity as I was soon to realise on discovering 3 of my slides were missing which I must have deleted the previous night. Thank goodness for backups!!

I headed up to Hall 8b, the venue for my presentation. It was a good size, around 170 capacity but didn't seem to big so I was ok with that. There was no session in there before me so had plenty of time to get setup and settled, or so I thought. The technician arrived soon after me and asked if I was going to be using the supplied laptop or my own, I said my own and he worringly replied, 'You are the first person who's wanted to use their own'. I replied 'Is that a problem?', and he assured it wasn't but there would just be a bit of setup to get it working.

Time went by, people started arriving and my opening slide was still no where to be seen on the big wall behind me. The room of around 50 people was filled with silence, Harrison my session chair did a good job of trying to break it with a quick poll of how many people had used APEX before, which was most (but not all) of the room. I then ran through my agenda which seemed like it took about 5 minutes but probably only lasted about 30 seconds. Anyway, with numerous trips back and forth to the control room by the technician, my opening slide finally appeared on the big wall, a sigh of relief from me and after all the waiting and anticipation, I could talk.

It went ok. I know there are a few things I could have explained better and in more detail. I had always been worried about the amount of technical information I was trying to get across in 1 hour (although originally I was worried I wouldn't be able to fill it!) and in hindsight, I think I should have gone for longer.

So having finished talking in about the right time, there were a few questions and the job was done. John Scott came up to me afterwards and introduced himself. It was great to finally meet him having only known of him before. He said my presentation was really good, which means a lot coming from someone like John and also suggested that I should have a longer time slot to get through everything, I definitely agree.

Me, full of relief!

For anyone who is interested in getting the slides I have currently taken them offline just because I wanted to change a few small things. Once these changes are made I will post a link on my blog. I am also trying to get a demo up and running which will have all the code available to download. Watch this space!

Having done my presentation, and with the weight of an elephant lifted from my shoulders I was then free to enjoy some of the conference.

Really enjoyed Zory Zeharieva of CERN present, 'A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN'. She presented well and covered some best practices for building scalable applications which was very interesting and also expressed some concerns around the way APEX application files are managed within versioning systems, a common concern which came up in a few places over the conference.

Also then dropped in on Oracle's Barry McGillin talking about, 'Consolidate Your Microsoft Access Applications to Oracle Application Express'. Barry is a great speaker, informative and easy to listen to (even at the end of a very long day!). He showed how to migrate an Access database with data into an Oracle database and then use the APEX 3.0 feature 'Application Migration Workshop' to build your APEX application. Well worth a look for anyone planning on doing this kind of thing.

So the day of presentations was over, and it was up to the free bar to relax and catch up with some people. Had a very pleasant drink with some colleagues and headed back to the hotel for a good night's sleep.

UKOUG day 2 done and dusted. The months of worry and preparation were worth it and my life can now resume normality. Presenting is, although nerve-wrenching and time-consuming, a very rewarding experience and I would recommend it to anyone.

Sunday, 2 December 2007

Problem with 'Not Null' validations

Introduction...
A question popped up on the forum this week about the ability to bypass not null validations. The problem is if you define a 'Not null' validation on a page item, the user can enter a blank space and the validation will allow it. But there is a simple workaround and it requires no changes to any of your existing validations.

Note this will only work in APEX version 3.0 or higher. If you are looking to implement this on 2.2 or 2.2.1, then please refer to the link above to the forum post where this was discussed, where I posted a solution for 2.2 (the views were changed slightly). Unfortunately, pre 2.2 this method is not possible, as I'm referencing the APEX dictionary views that were introduced in 2.2.

How...
To get around this you can use an APEX application process to trim all the items for the current page that have associated not null validations. Create the following application process:

Sequence: 1
Process Point: On Submit: After Page Submission - Before Computations and Validations
Name: TRIM_NOT_NULL_ITEMS
Type: PL/SQL Anonymous Block

Process Text:
BEGIN
FOR cur IN
( SELECT items.item_name
FROM apex_application_page_items items,
apex_application_page_val vals
WHERE items.application_id = TO_NUMBER(:APP_ID)
AND items.page_id = TO_NUMBER(:APP_PAGE_ID)
AND items.item_name = vals.associated_item
AND vals.validation_type
= 'Item specified is NOT NULL'
)
LOOP
apex_util.set_session_state( cur.item_name
, TRIM(v(cur.item_name)));
END LOOP;
END;

I added this to a form on EMP and setup a not null validation on the job item. Then loaded the page, keyed a space for the job field and on inspection of the debug, you can see that before the process fires, session state shows a " " for P12_JOB, which would have passed the not null validation.



And then after the process has fired, it has been trimmed and set in session to "", which causes the not null validation to correctly fail.



Hope it helps,
Anthony

Monday, 19 November 2007

Debugging with the APEX repository

Introduction...
I was recently working on an APEX application on a familiar 'Form on a Table' wizard-built page and encountering a ORA-00957 Duplicate Column Name error, meaning that a column name must be specified more than once in the INSERT.

So the problem must be that I had more than one page item bound to the same database column. This page had got quite bloated with lots of other business logic so I started by turning off regions, the old 'process of elimination', debugging through seeing if there was anything obvious, but nothing sprang up. Then I thought if only there was a way to view all the page items on my page which were bound to the same database column. Well there is, using the APEX repository.

For those of you who are not familiar with the repository, it is basically a set of views that expose all the APEX application metadata. For further information see Have a clean up, utilising the APEX repository which contains links to loads more information.


How...
In the repository there is a view called APEX_APPLICATION_PAGE_DB_ITEMS which is what we are interested in. It is described in the APEX_DICTIONARY view as...

'Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.'

We can then run the following query to return all the items for a specific application / page bound to a db column more than once.
SELECT   db_column_name,
SUM(1) Duplicates
FROM apex_application_page_db_items
WHERE page_id = :page_id
AND application_id = :app_id
GROUP BY db_column_name
HAVING SUM(1) > 1
ORDER BY SUM(1) DESC
(Note: This view does not contain conditional rendering information, so if this was required you would need to join to APEX_APPLICATION_PAGE_ITEMS on ITEM_ID).


Conclusion...
So if ever you think, I wish I could see this information about this page / report or whatever, you probably can, just take a look into the repository. I wonder if there would be any scope for an APEX debugging framework that defines sets of processes linked to common 'ORA' errors. So for this example, it would simply be:

Error: ORA-00957 - Duplicate column name
  • Step 1: Run the following query, binding in your page and application id.
    SELECT   db_column_name,
    SUM(1) Duplicates
    FROM apex_application_page_db_items
    WHERE page_id = :page_id
    AND application_id = :app_id
    GROUP BY db_column_name
    HAVING SUM(1) > 1
    ORDER BY SUM(1) DESC

  • Step 2: Investigate all rows returned from the query and unbind items which should not be bound to the database column.

  • Step 3: Retest your page.


That would be nice.
Anthony.

Survey results

Following the recent survey I conducted on this blog, 'What would you like to read more about on my blog?', the results were:

Developer Tips - 54%
AJAX General - 45%
AJAX with JSON - 45%
BI Publisher Integration - 33%
Access Migration - 0%

I will thus be focusing my efforts on Developer Tips and AJAX related posts in the near future. I was quite surprised at the 0% interest in 'Access Migration' and would have thought this was be quite popular as this is one of the main platforms systems are built on that APEX applications replace. Or maybe it's just that developers aren't using the migration functionality built in to APEX / SQL Developer and just doing it without looking at these. Interesting.

Thank you all for your feedback. More to come shortly.
Anthony.

Monday, 24 September 2007

What do you want to learn about?

In the interest of trying to write good posts that the APEX community will find useful, I have added a new 'poll' feature to my blog. My first poll is entitled simply, 'What would you like to read more about on my blog?' and I have suggested a few titles to pick from. You can cast your vote by selecting from the check boxes on the right of the page, below 'About Me'.

If you have any other suggestions or areas you wish to learn about or understand better, then please add a comment to this post.

Look forward to hearing from you!
Anthony.

Wednesday, 5 September 2007

UKOUG 2007 agenda packed with APEX

The agenda for UKOUG 2007 has just been released and there is currently a total of 13 presentations on or relating to APEX (and a keynote delivered by Tom Kyte!). They are...

Loads to learn so come along, I'll be there and presentating 'Building The Rich User Interface with Oracle Application Express and AJAX' so looking forward to doing that (scary!) and listening to many more! Hope you can make it!
Anthony.

Monday, 3 September 2007

Developer productivity gains with 'Bookmarklets'

Introduction...
The ability to manipulate web content with Javascript on the fly is becoming more and more widespread, with tools such as Firefox's Greasemonkey, IE's Trixie and a whole host of other browser add-ons providing the ability to apply scripts to web pages. Bookmarklets are probably one of the simplest and easiest ways to achieve this, storing Javascript code in the URL of a bookmark or favorite. I have built a few which I find helpful in automating some of the more mundane tasks whilst developing with APEX.


How...
All you need to do is drag the following links onto your bookmarks / favorites toolbar in your browser. They are all compatible with Firefox and Internet Explorer.
  • Bulk Edit - Repad sequence numbers - Sequence numbers by default are padded by 10, but as the development process ensues, items get moved, processes get added and sequence numbers inevitably get scrunched up. This bookmarklet repads sequence numbers by 10 when in bulk edit pages where the 1st column is the sequence number, such as bulk editing page items, computations or validations.

  • General - Enlarge textareas - Enlarges small textareas for easier editing, useful for 'Region Definition > Source' or 'Page Process > Source'.

  • Report Attributes - Alignment LEFT - Sets both 'Column Alignment' and 'Heading Alignment' to 'LEFT' whilst on the 'Report Attributes' page.

  • Report Attributes - Sort All - Checks all the 'Sort' checkboxes whilst on the 'Report Attributes' page.

Once this is done, you can just click the bookmark / favorite when on the appropriate page and the Javascript will be applied.


Conclusion...
In my opinion bookmarklets are fantastic for implementing these type of simple extensions, the main reasons are:
  • Portability(1) - All my bookmarklets will work in Firefox and Internet Explorer.

  • Portability(2) - And this is important, using a Firefox add-on such as Foxmarks which synchronizes bookmarks between different instances of Firefox (home and work), your bookmarklets will be available to you on any browser you have synchronized.

  • Simplicity - Minimal learning curve for deployment, assuming a basic understanding of Javascript and bookmarks.

  • Categorisation - Easy to organise your bookmarklets into folders and subfolders, just as you would with your regular bookmarks.

  • Ease of use - I have my bookmarklets available on my bookmarks toolbar, ready for quick deployment.

As APEX developers, we live in a world of 'browser-based development', rather than a traditionally rich IDE such as JDeveloper and Eclipse. The standard browser was not designed to be used as a development tool and thus anything we can do to help increase development productivity, such as write custom bookmarklets or use the essential Firebug can really help. So have a go and write your own, here are some general guidelines including a very useful bookmarklet builder. During development, I find a 'could I automate this?' attitude helps in identifying bookmarklet opportunities.

Let me know if you have any more useful productivity increasing bookmarklets to share and I'll add them to this post.

Anthony.


Update - Another way...
Following the advise of Patrick and Dimitri, I've taken a look at the APEX Builder Plugin and it really is awesome, congratulations Patrick!

It is compatible with both Firefox (with Greasemonkey) and IE (with IE7Pro), and it's fantastic. Whilst bookmarklets are great for a quick fix, if you want to get serious about building your own productivity customisations then I strongly advise you to invest a little time looking at this plugin.

It does not change the APEX base install in any way and just requires the developer to have one of the above add-ons installed in their browser and the plugin scripts available to the workspace, either on the server or stored in the database as static files. Within a couple of hours I had it installed, had a good look around and was building my own extensions.

One of the features of the plugin is the creation of a custom 'Set' select list at the top of any page in the Application Builder. This comes with some useful pre-rolled 'Setters', and also and importantly allows the developer to easily define their own 'Set' options for all or specific pages. This not only makes work more efficient for the developer, it can also be leveraged for enforcing standards across a team developing in APEX.

I was able to recreate the functionality from my bookmarklet, 'Bulk Edit - Repad sequence numbers' on all the bulk-edit pages accessible via the 'Page Definition' page, with the following code:
// Page Items

ApexBuilderPlugin.addSetOperation
( "General"
);
ApexBuilderPlugin.addSetOperation
( "Repad sequence numbers"
, { "f01": function(pField, pRow)
{
return pRow + '0';
}
}
);

For further information on how to do this, please refer to the How to Install guide.

As well as providing the ability to define your own 'set' operations, it also provides the ability to map keyboard shortcuts to common functions, such as F10 for 'Apply Changes' or F8 for 'Run Page' and a much neater solution to the enlarge textarea problem.

Great work Patrick!!


Another Update...
I am now delighted to announce that my 'Repad Sequence Number' customisation will be included in the next official distribution of the plugin. Thanks to Patrick for giving me a mention.


Further Information...
Bookmarklet homepage
General Guidelines
Bookmarklet Builder
Web Development Bookmarklets
Oracle bookmarklets by Eddie Awad for searching documentation
APEX Builder Plugin

Friday, 17 August 2007

New Oracle Magazine APEX / AJAX article

Just found an interesting article by David Peake (APEX product manager), in the September / October '07 edition of Oracle magazine, entitled 'Express 2.0'. This gives a brief introduction into implementing AJAX based functionality into APEX applications, and then goes onto a step-by-step example on how to implement dynamic tooltips.

Worth a look.
Anthony.

Update: David has just started his own blog so be sure to add it to your feeds!!

Tuesday, 7 August 2007

Javascript debugging, an alternative to alert()

Introduction...
This is a simple trick to provide a more flexible approach for displaying debugging output when working on javascript code in your APEX applications. It uses a couple of debugging javascript functions, print() and println() which will show your debug messages in the current page's footer. This allows for multiple messages to be output without having to click through each one, as is necessary with the alert() function. With other benefits too such as being able to copy the text output which is not possible with alert().

How...
1. First add the following line to your 'Page Template > Definition > Footer', at the beginning:
<div id="debug"></div>
(It doesn't really matter where this goes in the 'Footer' code, as long as it's before the closing </body> tag.)


2. Then we need to make the following two javascript functions available for use in your application. (The recommended approach would be to store them in a '.js' file and place this file on your web server.)
//print output messages to your page
function print(p_value){
$x("debug").innerHTML = $x("debug").innerHTML +
p_value;
}
//print output messages to your page with a line break
function println(p_value){
$x("debug").innerHTML = $x("debug").innerHTML +
p_value + '<br/>';
}

3. You can then call either print() or println() from within your javascript code.

Happy debugging!
Anthony.


Update...
Do you Firebug? Then there is a simpler way!

If you use Firefox with Firebug, then another way of debugging is to use the built-in Console API. This details some very useful calls such as:

console.log - Writes a message to the Firebug console

console.trace - Writes an interactive stack trace to the Firebug console with information about the calling function and also the parameters passed to the function. It is interactive in the sense that you can click the function name to take you straight to the function source, very nice!

This API provides more calls as well which look very useful, so it's worth taking a look.

(Thank you Carl!)

Saturday, 23 June 2007

Checking for Duplicates

Here is a quick way to check for duplicate records in the database, by using the 'group by' and 'having' sql commands.

The following example uses the trusty 'EMP' table and checks for duplicates in the 'JOB' column.
select   job,
sum(1) Duplicates
from emp
group by job
having sum(1) > 1
order by sum(1) desc

Or generically this query could be expressed:
select   [duplicate_column_name],
sum(1) Duplicates
from [table_name]
group by [duplicate_column_name]
having sum(1) > 1
order by sum(1) desc

Hope it helps!
Anthony.

Saturday, 16 June 2007

AJAX retrieval using Javascript Object Notation (JSON)

Introduction...
Typically, most of the AJAX examples out there for APEX either use plain text or xml for data transmission. This post details how to implement AJAX functionality using Javascript Object Notation or JSON, retrieving multiple items from an application process. JSON, or the fat free alternative to XML is a simple, lightweight syntax for representing data and can be easily integrated into an APEX application. Due to its light weight, it has performance advantages over using XML and is also very easy to understand and manipulate on the client side.

This post will show how to create a simple example of AJAX with JSON, where a user selects either 'Car' or 'Bike' from a select list, then either car or bike 'models' will be populated into 2 page items.


Pre-requisites...
In order to parse the JSON text and create the javascript objects in your javascript code, you will need to download the JSON open source parser. Once downloaded, it must be made available in your APEX environment. The best way to do this is to save the file into the following directory on your web server:
  ORACLE_HOME\Apache\Apache\images\javascript

And then reference it by adding the following to your 'Page Template > Definition > Header', just after the '#HEAD#' substitution string:
  <script src="#IMAGE_PREFIX#javascript/json.js"
type="text/javascript"></script>

Alternatively, if you have restricted access to the web server (as with apex.oracle.com), then upload the 'json.js' file as a static file, via 'Shared Components > Static Files', ensure you associate it with your application and then add this reference to the page template:
  <script src="#APP_IMAGES#json.js"
type="text/javascript"></script>
Note: The static file method will not be as fast in execution as copying the file directly on the server.


How...
Step 1: Create a new, blank page, call it 'AJAX - JSON Demo Page'. (I'll assume it's page 1 for the rest of this example.)

Step 2: Create a HTML, empty region, call it 'AJAX - JSON Region'.

Step 3: Create the following 3 page items:
- The activator will be a select list, where you either select 'Car' or 'Bike'. Selecting one of these options will invoke the 'onChange' event specified and call the 'Get_Multiple_Items()' javascript function, specified in step 5. Set the following:
Type = Select List
Item Name = P1_ACTIVATOR
Region = AJAX - JSON Region
List of Values Query = STATIC2:Car;Car,Bike;Bike
Label = Activator
Element > HTML Form Element Attributes = onChange="get_Multiple_Items();"

(All other attributes just leave as default.)

- Item 1 to return the first model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_1
Region = AJAX - JSON Region
Label = Return Item 1

(All other attributes just leave as default.)

- Item 2 to return the second model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_2
Region = AJAX - JSON Region
Label = Return Item 2

(All other attributes just leave as default.)


Step 4: Create an application process via 'Shared Components', set the following:
Name = RETURN_MULTIPLE_ITEMS
Process Point = On Demand: Run this application process when requested by a page process
Type = PL/SQL Anonymous Block
Process Text =
declare
return_item_1 varchar2(100);
return_item_2 varchar2(100);
return_str varchar2(2000) := '';
begin
owa_util.mime_header('text/html', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;

if :P1_ACTIVATOR = 'Car' then
return_item_1 := 'Ferrari';
return_item_2 := 'Ford';
elsif :P1_ACTIVATOR = 'Bike' then
return_item_1 := 'Suzuki';
return_item_2 := 'Ducati';
end if;

return_str := '{
"model":[
"'||return_item_1||'",
"'||return_item_2||'"
]
}';

htp.prn(return_str);

end;
(All other attributes just leave as default.)

Of particular interest here is the following lines of code:
  return_str := '{
"model":[
"'||return_item_1||'",
"'||return_item_2||'"
]
}';
So this is where we need to encode the result into the correct JSON syntax, so as to be parsed successfully in the client.

JSON syntax is exactly the same as the syntax used to define normal object and array literals in javascript. For a detailed description of the syntax, see the JSON homepage. In my example, if the user selects 'Bike', the following string would be returned by the process:
  {"model":["Suzuki","Ducati"]}
So here we have a single member called 'model', containing an array of the two return values, 'Suzuki' and 'Ducati'.


Step 5: Create a javascript function on the page
We now need to create the function that will setup, send and receive the request from the server. This will use the built-in 'htmldb_Get' ajax function to call the application process called 'RETURN_MULTIPLE_ITEMS', created in step 4. Set the following:
Page Attributes > HTML Header > HTML Header =
<script language="JavaScript" type="text/javascript">
<!--
function get_Multiple_Items() {
var get = new htmldb_Get(null,
&APP_ID.,
'APPLICATION_PROCESS=RETURN_MULTIPLE_ITEMS',
0);
get.add('P1_ACTIVATOR',$x('P1_ACTIVATOR').value);

gReturn = get.get();

if (gReturn) {
var myJSONObject = gReturn.parseJSON();
$x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];
$x('P1_RETURN_ITEM_2').value = myJSONObject.model[1];
}
}
//-->
</script>

Of particular interest here are the following lines of code:
  var myJSONObject = gReturn.parseJSON();
This calls the function 'parseJSON()' which parses the JSON text to produce an object, and then...
  $x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];
The resulting object 'myJSONObject' can then be accessed like any normal javascript object. In this case, we are retrieving the first value from a member called 'model' (which was created in step 4). This value then sets the 'P1_RETURN_ITEM_1' value.

Now run your page and you should be able to see some nice AJAX and JSON in action! Or take a look here.


Conclusion...
So it's a pretty simple little example, but I really like how easy it is to manipulate the object client-side. The only downfall is that the syntax to encode the JSON string in the application process takes a bit of getting used to, but I found it fairly easy to get my head around. Ideally, you would have a server-side encoder for this and there are many available, just none for PL/SQL that I've found*. There are lots available for Java, which could potentially be loaded in the database and called via a PL/SQL wrapper.

I have not done this for this example because I felt it was overkill for what I was trying to achieve. To just return a few items like I have done here, manually encoding the JSON text was fairly painless, although I'm sure it could quickly get very painful if creating large, nested structures, where a proper encoder may be more beneficial (and performance gains would be more apparent).

Also, bear in mind my example doesn't really do anything in the application process which warrants a trip to the database (just checking hard-coded strings, which could be done client-side). The purpose of this post was to highlight how to implement JSON with AJAX in APEX so I tried to reduce complexity elsewhere to focus on the main points.

Hope it helps,
Anthony.


* Update...
Since writing this article, it appears a PL/SQL JSON library has cropped up from France. I have yet to try it but will take a look and blog about it soon.


Further Information and Links...
Article: 'JSON: The Fat-Free Alternative to XML'
Javascript source file containing JSON parser
JSON homepage, including links to many server-side encoders
JSON wiki
JSON RFC 4627
Article: 'Speeding Up AJAX with JSON'
Carl Backstrom's APEX AJAX Site

Thursday, 7 June 2007

Have a clean up, utilising the APEX repository

Introduction...
Remember switching those conditions to 'Never' on various APEX components and forgotten where and when? Use the awesome APEX repository to have a tidy up.


How...
The following query will return all of the following APEX components that have a condition set to 'Never':
Breadcrumbs, computations, list entries, list of values, navigation bars entries, branches, processes, regions, report columns, validations, buttons, items, tabs and shortcuts.

The query allows for either viewing components by application, or by workspace. To view by application enter the application ID for the app_id bind variable, to view all applications in a workspace just leave app_id as null.
select
component,
description,
sequence,
location,
identifier,
app_id
from
(
select
'Breadcrumb Entry' component,
entry_label description,
null sequence,
'Page: ' || defined_for_page location,
breadcrumb_id identifier,
application_id app_id
from APEX_APPLICATION_BC_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'Application Computation' component,
computation_item description,
computation_sequence sequence,
'Shared Component - Logic' location,
application_comment_id identifier,
application_id app_id
from APEX_APPLICATION_COMPUTATIONS
where upper(condition_type) = 'NEVER'
union
select
'List Entry' component,
entry_text description,
display_sequence sequence,
'Shared Component - Navigation - List (' ||
list_name || ')' location,
list_entry_id identifier,
application_id app_id
from APEX_APPLICATION_LIST_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'LOV Entry' component,
display_value description,
display_sequence sequence,
'Shared Component - User Interface - LOV (' ||
list_of_values_name location,
lov_entry_id identifier,
application_id app_id
from APEX_APPLICATION_LOV_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'Navigation Bar Entry' component,
icon_subtext description,
display_sequence sequence,
'Shared Component - Navigation' location,
nav_bar_id identifier,
application_id app_id
from APEX_APPLICATION_NAV_BAR
where upper(condition_type) = 'NEVER'
union
select
'Page Branch' component,
branch_point description,
process_sequence sequence,
'Page: ' || page_id location,
branch_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_BRANCHES
where upper(condition_type) = 'NEVER'
union
select
'Page Computation' component,
item_name description,
execution_sequence sequence,
'Page: ' || page_id location,
computation_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_COMP
where upper(condition_type) = 'NEVER'
union
select
'Page Process' component,
process_name description,
execution_sequence sequence,
'Page: ' || page_id location,
process_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_PROC
where upper(condition_type) = 'NEVER'
union
select
'Page Region' component,
region_name description,
display_sequence sequence,
'Page: ' || page_id location,
region_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_REGIONS
where upper(condition_type) = 'NEVER'
union
select
'Report Column' component,
column_alias description,
display_sequence sequence,
'Page: ' || page_id || ' (' ||
region_name || ')' location,
region_report_column_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_RPT_COLS
where upper(condition_type) = 'NEVER'
union
select
'Page Validation' component,
validation_name description,
validation_sequence sequence,
'Page: ' || page_id location,
validation_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_VAL
where upper(condition_type) = 'NEVER'
union
select
'Button' component,
button_name description,
button_sequence sequence,
'Page: ' || page_id || ' (' ||
region || ')' location,
button_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_BUTTONS
where upper(condition_type) = 'NEVER'
union
select
'Page Item' component,
item_name description,
display_sequence sequence,
'Page: ' || page_id || ' (' ||
region || ')' location,
item_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_ITEMS
where upper(condition_type) = 'NEVER'
union
select
'Parent Tab' component,
tab_name description,
display_sequence sequence,
'Shared Component - Navigation - Tabs' location,
parent_tab_id identifier,
application_id app_id
from APEX_APPLICATION_PARENT_TABS
where upper(condition_type) = 'NEVER'
union
select
'Application Process' component,
process_name description,
process_sequence sequence,
'Shared Component - Logic' location,
application_process_id identifier,
application_id app_id
from APEX_APPLICATION_PROCESSES
where upper(condition_type) = 'NEVER'
union
select
'Shortcuts' component,
shortcut_name description,
null sequence,
'Shared Component - User Interface' location,
shortcut_id identifier,
application_id app_id
from APEX_APPLICATION_SHORTCUTS
where upper(condition_type) = 'NEVER'
union
select
'Tab' component,
tab_name description,
display_sequence sequence,
'Shared Component - Navigation - Tabs' location,
tab_id identifier,
application_id app_id
from APEX_APPLICATION_TABS
where upper(condition_type) = 'NEVER'
)
where app_id = decode(:app_id,null,app_id,:app_id)

The APEX repository views that are being used return different information for the different component types, so I have used generic place holders for the results of an inline view, which does a series of unions on queries retrieving data from the views. I have tried to ensure the what is retrieved for each component type is descriptive enough so as to be able to easily locate the component in your application builder and delete it. A sample result is shown below:


By the way, in writing this post, for an easy and accurate way to get a definitive list of all the apex components that could be conditionally displayed, I went to the 'APEX_DICTIONARY' view and applied the following predicate:
upper(column_name) = 'CONDITION_TYPE'
This returned a list of APEX views that have columns with the name 'CONDITION_TYPE'. These views were then used as a basis for my main query.

Tidy app, tidy mind.


Further information...
Patrick Wolf's excellent post and presentation slides on 'The Power of the Apex Repository'.

Browse Apache files from your browser

If, like me you have worked on APEX projects with restricted access to the server where the Oracle HTTP Server is installed, there are a few things you can do to view information stored on the server, directly via the browser.

You can look at:

1. Image files
A new feature of APEX 3.0 allows browsing of images that are part of the APEX distribution and available for use, located in following directory:

ORACLE_HOME\Apache\Apache\images\menu

From the 'Application Builder', from the shortcut menu click on the 'Find' torch, pictured below.


This will load a popup, where you can search a whole load of useful APEX stuff, of interest here is the 'Images' tab. Click this, ensure 'Show' is set to 'Standard Images' and you will be able browse the images currently available. By clicking on an image, APEX will also provide you with a link to be used in your application.


Note: This technique can also be used to view cascading style sheets available to you. These CSS shown here are solely the theme independent ones and thus always available to you regardless of theme.

If however, you wish to view a specific CSS file via the browser, read on...


2. CSS and Javascript files
You can look at other files that are contained within the images directory on the server (not just images!), via the virtual path specified in the following configuration file:

ORACLE_HOME\Apache\modplsql\conf\dads.conf

This can be done directly from the browser by removing everything after the port number and replacing it with a path to the file you wish to look at. This would typically be used to look up a '.css' or '.js' file that the current page references, something like:
src="/i/javascript/htmldb_html_elements.js"
Then use this reference at the end of your url, as shown in the picture below.



3. Activity Logs
Although you cannot see the exact access_log files from the browser, there is a report which shows similar information, which can be accessed via:

Home > Utilities > Application Express Views > APEX_WORKSPACE_ACTIVITY_LOG

This contains information like workspace, application, apex_user, page_id, view_date, think_time and ip_address to name a few.

Wednesday, 6 June 2007

UK market rate for an APEX developer

For you contractors out there, check out this link to the 'IT JobsWatch' site, where they have some interesting statistics on the current APEX market in the UK for the last 3 months.

When I checked on the 6th June, there were 22 advertisements for contracts in the last 3 months, with 6 of those listing a daily rate averaging £428, it seems the APEX wave is still swelling!

---------------------------
Quarterly Update: 4th September 2007
Adverts in last 3 months: 59
Average Daily Rate: £366 (from 10 adverts quoting a rate)

---------------------------
Quarterly Update (a bit late!!): 1st February 2008
Adverts in last 3 months: 52
Average Daily Rate: £359 (from 14 adverts quoting a rate)

---------------------------
Quarterly Update: 12th April 2008
Adverts in last 3 months: 66
Average Daily Rate: £421 (from 27 adverts quoting a rate)

---------------------------
Quarterly Update: 10th July 2008
Adverts in last 3 months: 60
Average Daily Rate: £442 (from 30 adverts quoting a rate)

---------------------------
Quarterly Update (very late): 12th December 2008
Adverts in last 3 months: 56
Average Daily Rate: £384 (from 14 adverts quoting a rate)