Monday, 18 October 2010

SAGE Computing Services welcomes Kylie Payne

While I was in wonderful Kong Kong, the mugshot arrived and I now have the content to announce Kylie Payne has arrived on our SAGE team:

http://sagecomputing.com.au/about_sage_computing_services.html#kylie

I've already had the opportunity to work with Kylie on a few things and I enjoy her great attitude. We have a similar background - working on a few data integration projects; and now we look forward to the growing world of Oracle Application Express.

To meet Kylie come by our booth at November's AUSOUG conference.

See you there!

ScottWe

Friday, 8 October 2010

Compute the area of...

Riddle me this
Somewhere in the world there must be an exam or test that asks the question to compute the area of a given shape using PL/SQL. I know this because occasionally I look at the Google Analytics for my blog and see some pretty crazy google searches that arrive at my page because of my chosen blog name.

In just a few hours I take off for Hong Kong to take the kids to Disneyland and visit the city for a few days. I'm happy, so (while I don't condone the use of the Internet to solve all your problems) I thought I'd make a few other people happy, and make the visit to my site worthwhile :-)

I've used formulas according to Maths is Fun, and also demonstrated a few other SQL rounding functions you can find well documented here. That's right kids, documentation is your friend.

declare
  lc_pi constant number := 3.141592;
  
  -- triangle
  ln_t_base    number default 2;
  ln_t_height  number default 4;
  
  -- square
  ln_s_length  number default 5;
  
  -- circle
  ln_c_radius  number default 200;
  
  -- ellipse
  ln_e_width   number default 3;
  ln_e_height  number default 2;
  
  -- trapezoid / trapezium
  ln_z_a       number default 2;
  ln_z_b       number default 5;
  ln_z_height  number default 3;
  
  -- sector
  ln_r_radius  number default 4;
  ln_r_degrees number default 45;
  
  ln_area  number;

begin
  -- triangle
  ln_area := 0.5 * ln_t_base * ln_t_height;
  dbms_output.put_line('Triangle: '||ln_area);

  -- square
  ln_area := POWER(ln_s_length, 2);
  dbms_output.put_line('Square: '||ln_area);

  -- circle
  ln_area := lc_pi * POWER(ln_c_radius, 2);
  dbms_output.put_line('Circle: '||ROUND(ln_area, -2));

  -- ellipse
  ln_area := lc_pi * ln_e_width * ln_e_height;
  dbms_output.put_line('Ellipse: '||FLOOR(ln_area));

  -- trapezium
  ln_area := 0.5 * (ln_z_a + ln_z_b) * ln_z_height;
  dbms_output.put_line('Trapezoid: '||CEIL(ln_area));
  
  -- sector
  ln_area := 1/2 * ln_r_radius**2 * ln_r_degrees / 180 / lc_pi;
  dbms_output.put_line('Sector: '||ROUND(ln_area, 5));
  

end simple_calcs;
/  

Triangle: 4
Square: 25
Circle: 125700
Ellipse: 18
Trapezoid: 11
Sector: 6.28319

PL/SQL procedure successfully completed.

See you on the other side of Hong Kong!

Thursday, 7 October 2010

Perth AUSOUG Conference Information 2010 (advert)


To all Oracle, E-Business Suite; J2EE; DBA; Enterprise Architects; Siebel Users; MIS Management; and Academia professionals interested in this years Australian Oracle User Group conference in Perth (and why wouldn't you be?), I've just published some information on our SAGE website.

Here is a page linking to an amazing brochure that details everything you need to know about the conference.

And here I detail times where you can catch our SAGE experts present; and also link to our past presentations.

Download the daily session program for more information.

There really is a great line-up this year, and as usual I look forward to catching up with local & international guests.

ScottWe.

Wednesday, 29 September 2010

Full outer join example

Recently I came across a scenario where I thought "say, a full outer join might hit the spot here"

It was a simple problem - we need to check that an old mapping table mapped all the old values to new values.

I figured doing a full outer join would assist making sure all values were accounted for.

Below I've cut down the actual output, but it highlights to me that the Yilgarn & Avon-Mortlock districts are not mapped to any new records, and there are number of new records that don't have a presence in the old tables.

SQL> l
  1  select distinct
  2    o.mapped_val
  3   ,o.old_district
  4   ,d.dst_name
  5   ,d.rgn_name
  6  from old_mapping_table o
  7  full outer join new_region_list d
  8  on d.dst_id = o.mapped_val
 
MAPPED_VAL        OLD_DISTRICT         DST_NAME             RGN_NAME
----------------- -------------------- -------------------- --------------------
               92 AVON-MORTLOCK
               93 YILGARN
               16 ALBANY               ALBANY               SOUTH COAST
               17 ESPERANCE            ESPERANCE            SOUTH COAST
               69 GERALDTON            GERALDTON            MIDWEST
               94 GREAT SOUTHERN       GREAT SOUTHERN       WHEATBELT
               14 KALGOORLIE           KALGOORLIE           GOLDFIELDS
               71 PERTH HILLS          PERTH HILLS          SWAN
               73 SHARK BAY            SHARK BAY            MIDWEST
                                       BUNBURY              SOUTH WEST
                                       GASCOYNE             MIDWEST
                                       GOLDFIELDS           GOLDFIELDS

Nothing more, just thought I'd share.

Friday, 24 September 2010

Off Topic - twttr

I've done it now, do you need to buy a vowel?

I'm not yet sure what my use of this will be like, but you can be assured there will be many personal posts as I think about issues that may be found at sites such as the JREF. So perhaps use that as a guide if you may be interested in following (I have set my profile to private at this stage)

No doubt if I my Oracle related network grows, my use may change. You never know, we may all even learn a few things along the way.

swesley_perth

West Aussies - have a great, sunny, grand final long weekend!

Thursday, 23 September 2010

Another freebie from Oracle

On the back of my recent discussions on SQL Developer, at the start of the week Mike Hichwa announced on his blog that SQL Developer Data Modeler is now free!

This announcement was also confirmed by Sue Harper on OTN.

How there is no excuse not to model your applications :-)

More information on this product is available here.

note - us Aussies and probably a whole bunch of other countries spell it "Modeller" - good thing Google Search doesn't care.

Wednesday, 22 September 2010

JQuery Error Page Replacement

Yesterday I commented on using JQuery to replace the standard JavaScript dialog boxes.

Another simple solution to pretty-up your application is to replace the standard error display.

I'm not so much referring to making the messages user friendly, Martin Giffy D'Souza covered that very well recently, and you may have also seen Roel's example in the past.

So to really summarise their posts, define an On-Demand Application process called GetErrorMessage.
BEGIN
  -- this is probably the simplest example available - limited by imagination.
  htp.p('error title'||'#'||wwv_flow.g_x01);
END;

The following needs to be in the Error Page Template section of your page template.
<script type="text/javascript">
$(document).ready(function() {
  raiseErrorHandler();
}
);
</script>
And most of this you may choose to leave alone, except for the highlighted lines. I place it in my application's JavaScript file:
function raiseErrorHandler(){
  vError = $(".ErrorPageMessage");
  vError.hide();
   var get = new htmldb_Get( null, $v('pFlowId')
                ,'APPLICATION_PROCESS=GetErrorMessage'
                ,$v('pFlowStepId'));
   get.addParam( 'x01', vError.html());
   gReturn = get.get();
   get = null;
   var errArray = gReturn.split("#",2);
   showError( vError, errArray[0], errArray[1]);
}


function showError(pThis, pTitle, pText){
 vText = '<div id="alert" title="'+pTitle+'">'
          +'<img src="/i/alert_error.gif">'+pText+'</div>';
  $(pThis).append(vText);
  $("#alert").dialog({
               bgiframe: true,
               modal: true,
               minHeight : 200,
               width : 600,
               closeOnEscape : false,
               close : function(){window.history.go(-1)},
               buttons: {
                   Ok: function() {
                     $(this).dialog('close');
                     $("#alert").remove();
                     window.history.go(-1);
              }}
   });
}

Then bang, you have a mildly more beautified application.

I scraped this example off the application I'm currently working on. We have not yet filtered the errors to display something more user-friendly - but you can read more Martin's post about that.

Basically instead of navigating to the default error page, you get this pop-up instead.

ScottWe