Oracle Analytic functions

Here's an excellent article that I always refer to when I am in doubt on using Oracle analytic functions...

http://www.orafaq.com/node/55

Merge statement

Merge statement is available in Oracle since version 9i. You can use this to perform INSERT and UPDATE all in one statement.

However note that running MERGE statement in parallel can cause error situations, this happens mostly due to the fact that each parallel process tries to insert a row that doesn't exist causing primary key violations.

Here's a simple example:

Merge into d
Using (select col1, col2, col3 from src_table) s
On (d.col1 = s.col1)
When matched then
Update set d.col2 = s.col2,
d.col3 = s.col3
When not matched then
Insert (d.col2, d.col3) values (s.col2, s.col3);

Finding duplicate rows in Oracle

Adding these to my utility sqls.

SELECT col1, col2, COUNT(*)
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1

SELECT *
FROM table1 a
WHERE ROWID !=
(SELECT MAX(ROWID) FROM table1 b
WHERE a.col1 = b.col1
AND a.col2 = b.col2)

Note: col1 and col2 are assumed to be the unique columns that identify a row.

Apex with AJAX - Display Thumbnail on MouseOver

This is my first major technical blog, so I am pretty excited about it. :)

I had to display a thumbnail when I click on a row, on further search, came across the following website that lists a lot of APEX/AJAX related things, so I decided to give that a try. There's a page where AJAX is used to display Item Help when the user does a mouse over a particular Item. I got a brainwave, hmmm how about I use that to display a picture instead? So here I was, breaking my head over it and was finally able to figure it all out after a couple of hours of hard work, without actually adding any application level items or processes, it's pretty neat. Check out the Statistics page in http://www.philadelphiacricketleague.com for a demo, mouse over the name of a player.

Here's what I did:

(Step 1): I had a report that displays a list of players. On this page, I added the following Javascript in the HTML HEADER section:

<script type="text/javascript">
function AJAX_PIC(pThis,pId){

this.dTimeout;
clearTimeout(this.dTimeout);
this.dGet = dGet;
this.dShow = dShow;
this.dCancel = dCancel;
/* var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=AJAX_ITEM_HELP',0);
*/
var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=',0);
this.dGet();
return;

function dGet(){
this.dTimeout = setTimeout("this.dCancel()",5000);
//get.add('TEMPORARY_ITEM',pId);
get.GetAsync(dShow);
}

function dShow(){
$x_Hide('rollover');
if(p.readyState == 1){
}else if(p.readyState == 2){
}else if(p.readyState == 3){
}else if(p.readyState == 4){

$x('rollover_content').innerHTML =
'<img src="p?n=' + pId + '" />';
$x_Show('rollover');
htmldb_IE_Select_Item_Fix($x('rollover'));
$x_Style('rollover','left',findPosX(pThis)+pThis.offsetWidth+5);
$x_Style('rollover','top',findPosY(pThis)-($x('rollover').offsetHeight/2)+($x(pThis).offsetHeight/2));
document.onclick = function(e){
dCheckClick(e);
}
}else{return false;}
}
function dCheckClick(e){
var elem = html_GetTarget(e);
try{
var lTable = $x_UpTill(elem,"DIV");
if(lTable.id!='rollover_content'){dCancel();}
else{}
}catch(err){dCancel();}
}

function dCancel(){
$x_Hide('rollover');
document.onclick = null;
get = null;
}
}
</script>

The above is a modification of the original JavaScript. Note, I commented out the application process, and also the related parameter. So basically there's no application item or process. Then I updated the code where the innerHTML is set, and changed it to a image tag. The pID value is basically the ID related to the image that's stored in the HTMLDB_APPLICATION_FILES table.

(Step 2): Then for the Report, added the following to the REGION FOOTER:

<div id="rollover" style="display:none;background:#FFF;width:150px;position:absolute;z-index:9999"> <div id="rollover_content" style="padding:4px;border:2px "> </div>

(Step 3): Then in the Report attributes, open up the column for which the thumbnail needs to be displayed, and added the following code in the HTML EXPRESSION for the Column Attributes:

<a href="" onmouseover="AJAX_PIC(this,'#ID#')">#NAME#</a>

Where ID is the value from HTMLDB_APPLICATION_FILES tables corresponding to the picture. Basically Oracle Apex displays any picture from the HTMLDB_APPLICATION_FILES table if you use the following syntax:

<img src="p?n=15900401113612336" />

The number above is the ID for the image.

I also came across another neat way to do this, but it needs you to upload a JavaScript file. The details are in Dimitri's blog, here's the URL.


That's about it! Please leave any feedback if you find this useful or streamline it further.

Applications created with Oracle Apex so far...

So far I have created the following applications using Oracle Apex:

Philadelphia Cricket League Website (http://www.philadelphiacricketleague.com)
Home Owner Association Portal (http://www.byershoa.com)
Cricket Club Portal (http://www.unitedcricket.us, http://www.crickethaverford.com)

See http://www.cricketclubportal.com for more details.

Hosting Oracle Apex applications

Ok, now that you have a handle on Oracle Apex and have developed an application which is ready for production hosting, where do you host? That's the dilemma I faced some time back and here's what I found from my research, will update this as I find more.

www.apps-express.com/hosting/ - an affiliate of www.revion.com - good on bandwidth/space and pricing, US East Coast based.
www.appshosting.com/ - they do offer a small/medium plan, the largest bandwidth/space I found, US West Coast based.
www.shellprompt.net - limited bandwidth/space, based in Europe, will have to deal with timestamp issues.

Oracle Apex

I will use this blog to post articles, tips related to Oracle Apex, which is a great tool that I have been using for past couple of years.