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)