Roels Blog

5 Cool Things you can do with HTML5 (p4)

noreply@blogger.com (Roel Hartman) — Tue, 10 Apr 2012 09:44:00 +0000

The fourth part of this series of posts will cover Notifications. When you are a user of GMail and using Chrome, you probably are familiar with those little boxes that popup when you've got new mail. Those type of notifications you can create in your APEX application as well - using HTML5!
Alas, at this moment, these type of notifications only work in Chrome. In Firefox you can add this extension so it'll work in that browser as well. The other ones will follow - sooner or later...
You can also use this plugin that mimics this feature - to be safe on every platform, but that one will appear in your browser, so that's not a desktop notification.
But how do you create a real desktop notification?
As usual with HTML5, you just need a few lines of code. The code below is executed in a Dynamic Action when a button is pressed:

function RequestPermission (callback) {
window.webkitNotifications.requestPermission(callback);
}
function showNotification(){
if (window.webkitNotifications){
if (window.webkitNotifications.checkPermission() > 0) {
RequestPermission(showNotification);
}
else {
window.webkitNotifications.createNotification(
"#APP_IMAGES#HTML5.jpg", //Put link to your icon here
"Notification",
"Now you are notified!").show();
}
}
else
{ alert('Sorry, you have to switch to another browser to use notifications - or use the other button ;-)');
}
}
//Run:
showNotification();

Just like geoLocations, the browser has to ask the user for permission to use Desktop Notifications (for a domain). Once the permission has been granted you can call the "show()" method, containing an image, the title and the actual message. Easy and cool, huh!

And you can even get completely wild by creating notifications that contain HTML or adding event listeners to your notifications - for instance doing something when the notification is closed. See this for some more examples.

See the working APEX example on : http://apex.oracle.com/pls/apex/f?p=HTML5:NOTIFICATIONS.

5 Cool Things you can do with HTML5 (p3)

noreply@blogger.com (Roel Hartman) — Fri, 06 Apr 2012 12:01:00 +0000

Location, location, location, it's all about location...

Probably one of the most frequently used HTML5 features is geoLocation. Why? Because it is very easy to use and cool too!
You can check the corresponding example page on http://apex.oracle.com/pls/apex/f?p=HTML5:GEOLOCATION.
So how does that work?
In fact it is just a few lines of code. First include the Google Javascript API (<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>).
The next step (on Page Load) is to actually get the current position of the browser device by
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(showLocation, handleError);
} else {
error('GeoLocation is not supported in this browser');
}
Where showLocation specifies the callback method that retrieves the location information. This method is called asynchronously with an object corresponding to the Position object which stores the returned location information. So you can use this function to actually draw the map and place the marker. And ErrorHandler is an optional parameter that specifies the callback method that is invoked when an error occurs in processing the asynchronous call. This method is called with the PositionError object that stores the returned error information. So when somethings goes wrong you can show an alert with your own message.
Depending on the security settings of the browser, your user might see a message like "apex.oracle.com wants to track your physical location". The user has to accept or deny access to this information. In the privacy settings of your browser you can see what sites have access to your location.

And for actually determining your position, Google uses either your IP-address - so that might be quite inadequate when you use proxies - or the GPS in your (mobile) device. For the latter type of device you can also use navigator.geolocation.watchPosition(showLocation, handleError);. Then that function will be called every time the Geo of your device changes. So you can draw a map of where the user of your app has been during the period the app is active (like Runkeeper does).

You can see the full W3C API description here.

5 Cool Things you can do with HTML5 (p2)

noreply@blogger.com (Roel Hartman) — Wed, 04 Apr 2012 13:05:00 +0000

As promised in the previous post in this series, this one will be about Web Storage.

Before the HTML5 era, you could store information on the client (browser) using a cookie. But that has two disadvantages (or limitations). First of all, a cookie's size is limited to 4096 bytes (but that differs over the different browsers and versions) and secondly - even more important - is that a cookie is always sent with every HTTP request, resulting in more data being sent over the wire. And that reduces scalability and decreases performance.

Using HTML5 you can use another type of storage, or even two types: sessionStorage and localStorage. In most browsers the storage is limited to 2.5Mb, but some go up to 5Mb or even unlimited. See this page for all limits per browser. And it is important to know that sessionStorage lives as long as your browser/tab is open - and is deleted on close, while localStorage stays on the client - even after a shutdown of the machine.

In a storage only key-value pairs are stored - so it's a sort of NOSQL ;-) . You can add or replace a value in your store using localStorage.setItem("test", "12345"); or shorter : localStorage.test="12345"; You can even leave the quotes out, while all values are stored as text anyway.

And similar, you can retrieve a value using localStorage.getItem("test"); or localStorage.test;

One more thing: you can only access contents of the storage in a page when the storage is created from the same domain - so no cross domain sharing of data!

You see the contents of the storage from a domain, by opening up the Javascript console and issue the localStorage command and inspect the contents of the object. Or, in Chrome, open up the Developer Tools (Ctrl-Shift-I) and check the Resources tab. The users of the APEX Developer Addon will see that this (nice!) tool uses the localStorage feature as well...

You see the full W3C defintion on Web Storage here.

How could this be useful in an APEX environment?

When you want to welcome a user back, with a message like "Nice to see you back, we haven't seen you since <some date/time>", you can store that last date in the database. But then a user needs a login, otherwise you wouldn't recognize him. If you store that information in the localStorage, you can use that to say hello again - assuming he didn't remove the localStorage and he uses the same browser ;-)

A more sophisticated use could be, when you think of a webshop. A lot of people do "window shopping" on the net: filling up their virtual baskets with stuff...but never proceed to the counter. If you issue some kind of submit or AJAX call, every time a user adds something to his basket, you get a lot of network traffic and database processing for...what? For nothing actually. So when you keep this shopping on the client side, and submit the contents of the localStorage.basket to the database if and only if the user presses the [proceed to checkout] button, you maximize the use of your resources. Positive impact on scalability and performance!

See a working example on : http://apex.oracle.com/pls/apex/f?p=HTML5:LOCALSTORAGE

Next post will be about ... GeoLocation!

5 Cool Things you can do with HTML5 (p1)

noreply@blogger.com (Roel Hartman) — Mon, 02 Apr 2012 14:03:00 +0000

Recently I did a presentation with the title "5 cool things you can do with HTML 5". I even did that presentation 3 times within a week: OUG Ireland had the premiere, then OUG Norway and OGH APEX Day as the last one of the week. I've planned the same - or similar - presentation for the upcomig Collaborate and OUG Bulgaria conferences.

As the most stuff I present is demo (the slide deck is just 5 pages), people frequently ask whether I could write blogpost on one of the subjects. So why not create a sequence of 5 posts....that should make sense.

So this is that first of five posts. I hope, not promise, to finish it within a week or two...

Cool thing 1 - Input Types

With HTML5, you can use both new Input Types as well as additional Attributes. New Input Types are URL, email, number, search and more - see http://www.w3schools.com for a complete list. The definition is very straightforward. In your HTML, replace type="text" with type="email". So the definition is something like : <input type="email" value="" size="30">.

In a regular, desktop, browser that change doesn't seem to do anything, but if you visit that page using a mobile browser, you definitely notice the difference: (On an iPad/Phone/Pod) the virtual keyboard changes and will contain a "@" sign. And something similar happens for URL's (a ".com" key will appear) and numeric fields. And that's all without any coding!

But in an APEX environment you can't natively use these kind of fields - unless you write all the code the retrieve and store the information yourselves. Luckily there is a plugin available (on http://apex-plugin.com/) that bridges that gap. And even better...in APEX 4.2 these item types will be 100% native available!

Apart from the new input types, you can also use the new attributes as defined on http://www.w3schools.com. Two of those new attributes are particulary cool : Placeholder and Required. The "placeholder" attribute specifies a short hint that describes the expected value of an input field (e.g. a sample value or a short description of the expected format). The hint is displayed in the input field when it is empty, and disappears when the field gets focus.
The "required" attribute - not surprisingly - specifies that an input field must be filled out before submitting the form. You can set these attributes using the "HTML Form Element Attributes" property of any (text) field. And when you combine that with a CSS3 style setting using a pseudo class ":required" selector, like
:required {
border-color: #88a;
-webkit-box-shadow: 0 0 3px rgba(0, 0, 255, .5);
}
you get the red boxes around the input field.

Custom data attributes
Another new HTML5 feature is the support for custom attributes inside HTML elements. In the old world you could add an item using:
<input type="text" id="Roel" value="Roel Hartman" book="Expert Oracle APEX" twitter="RoelH" />
So you could use any custom attribute name you can come up with. In HTML5 however, you should prefix your custom attributes with "data-". So the HTML for the example above should be:
<input type="text" id="Roel" value="Roel Hartman" data-book="Expert Oracle APEX" data-twitter="RoelH" />
Having done that, you can easily access your custom attributes in Javascript:
$("#Roel").data().twitter;
And you can also create, change or remove the data-values by something similar to:
$("#Roel").data().testing="Test123";
(or using the setAttribute, getAttribute and removeAttribute methods).

The last thing I would like to mention is the "speech" option, see a previous blogpost (http://roelhartman.blogspot.co.uk/2012/01/wouldnt-you-like-to-talk-to-your-apex.html) for more info on that one!

You can see them all in action (and whether your browser supports it or not) on : http://apex.oracle.com/pls/apex/f?p=HTML5:INPUTTYPES

The next post will cover Web Storage!

Create a PDF document containing dynamic images

noreply@blogger.com (Roel Hartman) — Fri, 17 Feb 2012 13:42:00 +0000

For a current project we had the requirement to publish a more or less "glossy" PDF document containing all information about a certain object. One of the bits of information was...an image.

The first trial we used the APEX_WEB_SERVICE.BLOB2CLOBBASE64 function to convert a Blob to clob with base64 encoding. In the RTF Template (used in Word with the BI Publisher plugin) you can reference it using this code :

<fo:instream-foreign-object content-type="image/jpg">

<xsl:value-of select=".//PHOTO"/>

</fo:instream-foreign-object>

(where PHOTO is the alias of the clob column).

This works....until you're using a slightly bigger picture than a tiny thumbnail. In that case you hit the 32K limit per report column. Although Marc Sewtz (from the APEX Development Team) sort of "promised" to lift this limit in his blogpost of Jun 13 2008 (!) (http://marcsewtz.blogspot.com/2008/06/one-question-about-pdf-printing-feature.html), it is still the current limit.

The workaround - or other approach - is to call an image with a http request in the report definition. Therefore you have to create your own "display_image" procedure that downloads the image. Be sure to grant execute on this procedure to APEX_PUBLIC_USER or ANONYMOUS (depending on your configuration), and modify the WWV_FLOW_EPG_INCLUDE_MOD_LOCAL function when you're using the EPG (see http://daust.blogspot.com/2006/04/xe-calling-stored-procedures.html).

Next, change your query with a call to that procedure:

apex_application.get_g_base_href||'#OWNER#.DISPLAY_IMAGE?P_OBJECT_ID'||ID as IMAGE_URL

In your RTF Template you have to embed a dummy image and set the "Alt-Text" (via Size in the Image menu) to url:{IMAGE_URL}.

The first result was : It works for HTML output and Excel output...but not for Word (RTF) or PDF. Why this difference? No idea!
My setting was like this:

So the "POPULAIRE_NAAM" was visible on the report, but the image wasn't. And everything else below - all 8 ROWSETS - were visible as well. FYI: The grey border above with the text "Geen afbeelding beschikbaar" is an image itself, so it's not some image missing from this blogpost ;-)
After a lot of trial and error and Googling around, I called to the OTN Forum for help (see thread here : https://forums.oracle.com/forums/thread.jspa?threadID=2347610&tstart=0). After a few hours Carsten Czarski of Oracle Germany pointed to an elaborated example he put on the web - a long time ago, looking at the screen prints : http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/pdf-dyn-images/index.html. Because it was in German, it never popped up in my Google search..while all the components where in there! So I created a very very simple example to mimic his case. And then the image did show up!
So what did I do wrong?

It seems that the [for-each ROWSET1_ROW] loop must be defined before the image (although in my case that rowset always contains just one entry). It didn't matter for the "POPULAIRE_NAAM" field, but it did for the image (in Word/PDF, not in HTML/Excel).
So the correct setting is:

and now the PDF can contain images of any size....

Create a calendar item returning week numbers

noreply@blogger.com (Roel Hartman) — Mon, 23 Jan 2012 12:39:00 +0000

When you use the built-in jQuery UI datepicker in your APEX page, you choose from a number of different format masks...but something with "week" is not one of them. And guess what I needed for recent project...
But, the good news is, you can (rather easy) use the jQuery framework to tweak the result that's returned from the datepicker.
Just create a Dynamic Action that fires on load of the page, and add these line of Javascript:
//Show the week number as the first column
$( ".datepicker" ).datepicker( "option", "showWeek", true );
//Set Monday as the first day of the week
$( ".datepicker" ).datepicker( "option", "firstDay", 1 );
//Return yyyy-ww instead of the actual date
$(".datepicker").datepicker("option", "onSelect",
      function(value, date)
      { var week=$.datepicker.iso8601Week (
              new Date(date.selectedYear,
                       date.selectedMonth,
                       date.selectedDay));
        $(this).val(date.selectedYear+'-'+(week<10?'0':'')+week);
      }
);
That's it. Of course you have to replace the jQuery selector - $(".datepicker") - with the one that matches your selection.
You can also substitute the function that generates the week number with your own one. And, yes, you could turn this into a plug-in if you like... (maybe I will when I've got the time, but no guarantee).

Wouldn't you like to talk to your APEX app?

noreply@blogger.com (Roel Hartman) — Tue, 10 Jan 2012 13:28:00 +0000

Of course you are familiar with Martin Giffy D'Souza's blog talkapex.com (if you're not, check it out - it's really worthwhile). But wouldn't it be nice to not only talk about APEX, but also to APEX?

The only thing you need is a recent version of Chrome (11 or up) and a microphone. And then...just by entering "x-webkit-speech" to the HTML Form Element Attributes of your input item, you have speech-enabled your application!

All "speech enabled" items will get a little mic at the end. When you click on it, you can say what you like. Oh yeah..and it works for number and date fields as well!

One remark : Whatever you enter will go through Google's voice recognition ... so Big Brother is listening in on you!

Try it out yourself on : http://apex.oracle.com/pls/apex/f?p=HTML5:SPEAK

APEX app running native on iPhone - the details

noreply@blogger.com (Roel Hartman) — Wed, 28 Dec 2011 11:11:00 +0000

A (rather long) time ago, I wrote a blog post showing a demo of an APEX application running native on an iPhone. Now, finally, the moment is there to explain how I did it ;-)

The key part of this demo, is the use of PhoneGap. PhoneGap "wraps" any HTML5 code, thereby giving you access to the native API's of your device. This solution is also used by the upcoming ADF Mobile solution, but can already be used now for any HTML5 web application..including your APEX application.

Start with downloading the PhoneGap sources from their site and install it in your development environment (I used Apple's Xcode) - see the documentation on the PhoneGap site on the how-to.

Within Xcode, you'll get an index.html, which is the starting point of your application. In this, simplified, example, my index.html only contains the following code:

<!DOCTYPE html>

<html>

<head>

</head>

</body>

</html>

So the only thing it does, is a redirect to my APEX application. Nothing more, nothing less.

In the page template of the APEX application I included the phonegap-1.2.0.js script file (currently 1.3.0 is the latest version).

Before any features can be used, PhoneGap has to be started. In my example, I added a Dynamic Action on Page 0, that runs on Page Load, with as contents

document.addEventListener("deviceready",onDeviceReady,false);

And from that point on, any PhoneGap feature can be used. In the example in the blogpost mentioned above, I added a button on a form page, that executes the following snippet of Javascript:

var contact = navigator.contacts.create();

var name = new ContactName();

name.givenName = $v('P9_CUST_FIRST_NAME');

name.familyName = $v('P9_CUST_LAST_NAME');

contact.name = name;

contact.save(onSaveSuccess,onSaveError);

function onSaveSuccess( contact ){

navigator.notification.alert("Contact saved on your device");

}

function onSaveError(contactError){

alert("Error = " + contactError.code);

}

And that's it! When pressing the button, the contact - just the first and last name - is added to your contact list on your device. Of course you can add addresses, telephone numbers etc as well. And use other native device functionalities, like the camera or the compass as well. See the API documentation for all the details.

The only "problem" will be, that this native iPhone "application" - which is just the index.html file - will never be accepted in the Apple store, just because it hasn't enough content to act on it's own. So you have to build an app with more content, and use your APEX application just to fill in some "gaps". But there are of course more ways to get data from your database to your native application, like web services.

Please, let me know whenever you get an app in the store with some APEX content!

Happy PhoneGapping!

UKOUG2011 Retrospective

noreply@blogger.com (Roel Hartman) — Fri, 09 Dec 2011 19:21:00 +0000

Looking back at this weeks UKOUG Conference, a few things strike my mind. At first, there was no snow this time! A lot of wind to make my flight back quite wobbly, but no snow - like last year - that would make travelling very hard (up to almost impossible).
Second, there were - again - lots of good sessions to choose from. Several time slots had two or even three sessions I would like to attend. And another pro - as strange as it may sound from an APEX geek - there are not that many APEX sessions. That gives me the chance - or the obligation - to look into other corners of the Oracle ecosystem. So I actually did learn something!
And I saw some "ok" sessions, some good ones and a few excellent ones - like Doug's SPM, Connor's Partitioning and Slavik's SQL Injection. But... there was only one really impressive one: Cary's keynote. In a very emotional and personal way, he took the audience by the hand - telling some touching real life stories about his private life and work. You were right, Cary, to take this next step. Thanks a lot!

I've been to DOAG!

noreply@blogger.com (Roel Hartman) — Fri, 18 Nov 2011 14:42:00 +0000

This week I attended my first DOAG (German Oracle User Group) Conference. The conference was hosted in a part of the Nürnberg Conference Centre. The venue was excellent : All sessions where in the same area on three different levels - so everything was in a short walking distance. The conference rooms itself where very good as well: a stage, just slightly higher than the audience, perfect lighting, good sound and screen quality and a wireless mic. Everything a presenter - and an attendee as well - needs!
Something else that DOAG does better (not necessary healthier) than most other conferences: you can have food, snacks and drinks all day long. Not only during "coffee break" or lunch you can have slices of pizza, hot dogs or sweet cakes, but really all day long. And the quality of lunch and dinner was excellent as well: three courses and plenty stuff to choose from.
Then onto the content itself. There were 20 concurrent sessions, all started right at the hour (with a bell sign!), in al lot of different tracks, like: Database, Datawarehouse, BI, Development, Java, MySQL etc. Apart from some sidesteps now and then, I mainly focussed on the APEX Development area (no surprises here). I was very curious what the German speaking community does with APEX, as I wasn't familiar with most speakers. And I guess I am being spoilt by conferences like KScope, as a lot of the content couldn't adhere to those (high) standards. But the audience seemed to like it, so it probably complies with the overall expectations... (And I should more often go to sessions where I know nothing about!)
And what about my own presentation? I think it went pretty well, although I ended somewhat early. I rushed a bit though the first part, because a slot lasts "only" 45 minutes, so I had some time left in the end. But I think it came across quite well. Not that I got that many questions during my presentations (usually Germans don't interrupt the speaker) and just a few at the end - maybe due to the fact they thought they had to ask their question in English, which wasn't necessary of course. But in the next few days I got more questions during or between other sessions ;-).
Another minor thing, while I can understand German pretty well (about 95%), I really do prefer English for these kind of tech conferences... But still I might come back next year!!
So, next on the agenda, in a few weeks...UKOUG in Birmingham!

APEX Application running native on an iPhone

noreply@blogger.com (Roel Hartman) — Wed, 09 Nov 2011 20:21:00 +0000

This video shows how native an APEX application can run on iPhone. And yes, it is really an APEX application you see. And the whole movie is just one take.
You can see how a customer from the standard APEX Demo Application - "re-templated" to an Mobile APEX Application - is stored as a regular contact on your iPhone.

The "blind auditions": Evaluating Kscope12 abstracts...

noreply@blogger.com (Roel Hartman) — Tue, 08 Nov 2011 09:20:00 +0000

Last week (and both weekends) I evaluated all the abstracts for ODTUG KScope12 in two tracks: APEX and Developer's Toolbox. In the APEX track 76 abstracts where submitted, Developer's Toolbox has just a few more, 82. All the abstracts will be evaluated by a team of (around) 8 people, so it doesn't all depend on my rating ;-)
For the first time, the abstracts where anonymous. So you don't know who wrote the abstract when rating it. In some cases, you can guess (and some had their own name in the abstract or summary, so that's easy). But I have to say, it makes it more difficult to evaluate. Because, for some people it doesn't really matter what the abstract says, you know it will be good anyhow (and for other ones, it is just the other way round). But luckily we have to rate the presenters as well - as far as you know them.
We had to rate every abstract between 0 and 5, where 5 is a top one. My totals are: little under 20% I rated as 5, almost 40% got a 4, 30% received a 3 and the rest is 2 or lower.
The hottest subject in the APEX track is, without any doubt, mobile development. Seems like a lot of presenters are anxiously awaiting the next APEX release. In Developer's Toolbox, the subjects are more diverse, but, to my surprise, analytic functions are a trending topic! Not that these are new, but there seems to be a more general adoption and interest in this feature.

And what did I learn from these "blind auditions"?
That it is very hard to write a good abstract. And when you submit one, there always a line saying: Don't copy your abstract as a summery (or the other way round). They have two different goals. The abstract should convince the evaluator to select your presentation, while the summary should convince the conference attendee to attend your session. So you should give away more in the abstract - but please, not pages long - and maybe just make people curious in the summary part.

But seeing all these great abstracts, I am really sure that Kscope12 will be even better than this years!

I revamped my blog

noreply@blogger.com (Roel Hartman) — Tue, 08 Nov 2011 08:54:00 +0000

I revamped my blog recently by enabling the latest Blogger feature: Dynamic Views. You can pick your own view using the "Magazine" menu on top.
I hope you like it. I do ;-).

In two weeks: My first DOAG Conference

noreply@blogger.com (Roel Hartman) — Mon, 31 Oct 2011 10:04:00 +0000

From November 15 to 17, the annual DOAG (German Oracle User Group) Conference will be held in Nuremberg. I've never attended this conference before, so I am really curious how my experience will be - especially compared to the other big European Oracle event, the UKOUG conference three weeks later. The agenda looks very promising. A very neat and tight three full days, with a new 45 minute session starting on the hour. Just like you expect from Germans ;-). And with 20 parallel sessions it seems about the same size as the UKOUG. The nice thing is, there are a lot of (German) speakers on the list, I've never seen before. So that could be interesting. And there are a lot of APEX sessions as well - see my schedule below, where I colored all the APEX sessions in blue! Some sessions will be translated - from German to English, some of them will be in English, but the majority will be in German. So a good opportunity to dust off my German... In the flyer, sessions are marked when they're in English. Mine isn't but I wasn't planning on doing it in German... But we'll see, maybe I'll end up in "Genglish" or "Engman"...
You can take a look at the whole program here.
Oh, yeah, one shameless plug : My session on the XFILES (about the power of XML-DB used for creating an APEX Application - and with version control for APEX itself as an example) is Tuesday at 4PM...

Analytic function to the rescue! Again.

noreply@blogger.com (Roel Hartman) — Tue, 25 Oct 2011 08:21:00 +0000

My current APEX project has a requirement to show a chart on one of the pages. No big deal. Usually. Because it should represent some value over time and that value would be stored in the database....could be every second, this chart could contain 10,000's, if not 100,000's points.
So generating the XML, transferring the XML to the browser and interpreting the XML by the chart engine....was slow...
So I had to come up with a solution to reduce the number of points, without destroying the goal of the chart. Oh, and did I mention that the value could be stored every second, but could also be every minute, hour, whatever?
The first thing I came up with was the SAMPLE clause. Never heard of it and never used it before. You can just do a SELECT * FROM EMP SAMPLE(10) and as a result, you'll get 10% of the rows of the EMP table. The only withdrawal with that was, the result could be different every time. So when refreshing a chart, the chart could look really different. Another, more minor, hiccup was, the sample size should be hard-coded and couldn't be parametrised (another "smaller" subrequirement).
So after some research I stumbled upon an Analytic Function, that might do the trick: NTILE( number ). This function "divides an ordered data set into a number of buckets indicated by the number parameter and assigns the appropriate bucket number to each row" (quote from the documentation - couldn't say it better). So using this function, you can equally divide 100,000 records in 25 buckets - order by timestamp. And once you've done that you can easily calculate the average value per bucket. And the average timestamp as well. And just use these values to generate a more minimalistic XML document... O, and another fine thing: you can pass any number parameter to NTILE, so using the same query, generate either 10 or 10,000 points...

As a - functionally useless - example a query to generate 15 rows with the average object_id and the average create date of all_objects:

select  to_char( startdate + ( enddate - startdate )/2
               , 'YYMMDD HH24:MI:SS' ) label
,       average
from
(
select min(created)  startdate
,      max(created)  enddate
,      avg(value)    average
from
(
select object_id value
,      created
,      ntile( 15 ) over ( order by created ) as bucket
from all_objects
)
group by bucket
order by 1
)

"I am running for the ODTUG Board of Directors"

noreply@blogger.com (Roel Hartman) — Thu, 20 Oct 2011 10:46:00 +0000

No, not it's not about myself, but that's what my dear friend Martin Giffy D'Souza told me during last Oracle Open World. And I support his nomination whole hearted!

I know Martin since about 4 years since we met at one of the KScope conferences. Since then we'll meet each other twice a year, but are in touch more frequently by email, twitter and other social networks. Martin is passionate about his job and, knows really a lot about APEX and is always willing to share his thoughts, ideas and vision with others. And I think nobody is better suited for the ODTUG Board as Martin is!

If you wonder what Martin looks like...see the picture below, taken during the OOW Appreciation Event. He is the guy with the blue circle around his head ;-)

If you are an ODTUG member, you can vote here.

You can read Martin's official campaign and bio below...

Campaign Statement

I have attended ODTUG Kaleidoscope for several consecutive years and have been a presenter for the last three. The conference has allowed me to develop strong relationships with many others in the community, and the importance of these relationships has proven invaluable. I continually strive to give back to the community, using my personal time to answer questions through email, blogs, the Oracle forums, and by writing technical books. I would like to continue this spirit of giving back by joining the ODTUG Board of Directors.
As a new board member I will bring a fresh perspective and out-of-the-box ideas to help promote ODTUG and deliver our message to the world. I am fortunate enough to have a successful blog with several thousand unique monthly visitors. It is through this platform, along with other opportunities such as my consulting firm blog, social networking, and the multiple annual conferences that I attend from which I intend to help share the ODTUG mission and values.
The Board plays a pivotal leadership role as both a driving force and a face of the ODTUG community. I feel that my professional experience as a leader and mentor will help the Board guide and develop ODTUG for the future. The Board has responsibility to its most important group - the members. I will help ensure that the Board serves as both a voice and an ear for the entire ODTUG community; developers, DBAs, and technical experts of all things Oracle.
Many thanks for your consideration.

Biographical Statement
Martin Giffy D’Souza is an Oracle ACE and award winning presenter and speaker. Most recently Martin was honored with the ODTUG Kaleidoscope 2011 Presenter of the Year award. Martin also serves as a Co-founder & CTO at ClariFit Inc., a consulting firm specializing in Oracle solutions. Martin’s career has seen him hold a range of positions within award winning companies and his experience in the technology industry has been focused on developing database-centric web applications using the Oracle technology stack. Martin is the author of the highly recognized blog www.TalkApex.com, and he has co-authored several APEX books including Expert Oracle Application Express, a collaboration of some of the most renowned APEX developers in the industry. He has presented at numerous international conferences such as ODTUG, APEXposed, and COUG. Martin holds a Computer Engineering degree from Queen’s University in Ontario, Canada.

OOW2011 - Announcing the APEX Marketplace

noreply@blogger.com (Roel Hartman) — Thu, 06 Oct 2011 20:42:00 +0000

With the Oracle Database Cloud Service, Oracle also announced the APEX marketplace - a sort of App Store for APEX or APpEX Store if you like. When the Cloud Service is released, there will be number of free APEX Applications available for install. These are all created by Oracle itself and are very similar to the "Packaged Applications" that where available on OTN earlier. So if you're wondering why they're not available up there anymore and where they are gone: You got your answer now!
Just like any other APEX Application you can (probably) still export that application and install it on your own environment. But they will ne "locked down", although it's not quite clear what that actually means. Probably, to prevent support issues due to your own changes, you aren't allowed to make changes. BTW, not all Markterplace Applications will be available on OTN, some will, others won't ..
Another, not yet implemented, idea is that you can also upload your own application into the Marketplace. The application will be reviewed by Oracle before it's made available. Your application won't actually be up there, but an interested user will be redirected to your own site and be able to download it from there. And the idea is that you can either provide these applications for free or charge for it. Don't expect it to make you a millionaire, but, hey, that should go dollar-by-dollar anyway...
As a teaser a screenshot of the applications that will be available from the start:

Location:Ellis St,San Francisco,United States

Five things you (probably) don't know about PL/SQL

noreply@blogger.com (Roel Hartman) — Thu, 06 Oct 2011 18:28:00 +0000

This post is a (live) report from Tom Kyte's session with the title above he did in a packed room on Thursday morning on OOW2011.

1. Trigger trickery
A before row trigger uses consistent read. So it uses the situation as it was when the statement started. So during long running updates the actual situation might differ from the 'consistent read' situation. That might lead to a rollback and re-fire of the statement, and thus the trigger as well. So every before statement and row level trigger (apart from the last row) might fire twice!
So don't do anything you can't roll back in a trigger. If you call some autonomous auditing function in a trigger, you might encounter rows in your auditing table that didn't actually happen...
Direct path loads bypass triggers...so triggers don't always fire!
So, if you can avoid triggers...please avoid triggers.

2. Ignore errors
Error handling is done wrong more often than it's done right. Only catch exceptions that you are expecting - which means they aren't real exceptions anymore (like a NO_DATA_FOUND, but then in the same block as the SQL statement itself and not a general one at the end of your code). All the other ones should be raised, either immediately or at a later moment in the transaction.

3. Elaboration code
You can use instantiation code in a package body, by defining an anonymous block within the body. It runs one time only per session (and after ever reinstantiation).

4. Implicit conversions
Always use explicit conversions. Especially when you're relying on specific NLS settings, for example when converting dates! Implicit conversions might even lead to SQL injection by tweaking the NLS_DATE setting!!!
Relying on implicit conversion also might have a performance penalty, because the conversion takes CPU time and has impact on the access path the optimizer comes up with.

5. Roles
You need direct grants on an object in order to use that object in a PL/SQL object, roles don't work - on purpose. If you use "invoker rights", your code uses the roles and grants of the user who runs the code. Default code is created using "defined rights" and then the code uses the grants of the definer. Especially when you use invoker rights, you could encounter unexpected results, because table T of the definer might not be the same table T of the invoker....

Location:Cyril Magnin St,San Francisco,United States

OOW2011 - Announcing SQL Developer 3.1 New Features

noreply@blogger.com (Roel Hartman) — Wed, 05 Oct 2011 23:02:00 +0000

The new SQL Developer version 3.1, contains a lot of new functionality. For instance a lot of DBA functionality that was already available within Enterprise Manager is exposed in SQL Developer. The developers are using the exact same code, but with a SQL Developer skin on top of it.
Another very neat feature is the SQL Developer Cart. You can drag and drop any object in the cart and it will automagically create a sql script file and zip it. I see certainly a use for that, for instance to deploy an new version of an application : all files neatly packed together...
And of course, there is the Cloud Services. You can connect to your Oracle Database Cloud Service from within SQL Developer. Under the covers a RESTful web service is called and the results are processed. There is also functionality implemented to transfer data to and from the cloud, using DataPump. If you upload data into the cloud, the data pump file is automatically created, uploaded en processed. Very neat!

Location:Ellis St,San Francisco,United States

OOW2011 - Announcing the Oracle Public Cloud

noreply@blogger.com (Roel Hartman) — Wed, 05 Oct 2011 22:45:00 +0000

Until this Wednesday, Oracle`s statement was "use Oracle systems to build your own cloud", but this is changed drastically now! Starting November 1, Oracle does this for you. Just subscribe and within 20 minutes you are up and running in the brand new "Oracle Public Cloud".
So what do you get after this 20 minutes of waiting? It depends on what you ordered ;-). Within the Oracle Public Cloud (OPC), the following options are available:
- an Oracle Database Cloud Service
- a Java Cloud Service
- a Social Connect Cloud Service, based on WebCenter
- some Fusion Applications Cloud Services: CRM and HCM.

The Oracle Database Cloud Service
The Oracle Database runs on Exadata. Within a database you get your own schema(s) and tablespace(s), so while the database itself is shared with others, all data is partitioned and caged. To enhance security, Transparent Data Encryption will be switched on, and DataVault might also be used (still under consideration). You can connect to the database over http, rest and JDBC. You can order a small, medium or large service - ranging from 50 up to 250 GB storage space. The data transfer is limited to 6 times the storage.
When using this service it might be important where your data is stored (for instance when you don`t want to expose your data under the American Patriot Act). Therefore, next to the current datacenter in Austin (TX), an European center will be opened in Edinburgh, and more local centers are planned.
You can access the database via the APEX Listener, using RESTful web services. So any language that "speaks" REST, can use this service. Another option is to sign up for an APEX environment. Within a few minutes, you can start developing or deploying your APEX application in the cloud. Similar to apex.oracle.com, but in this environment you are allowed to run production applications!

The Oracle Java Cloud Service
With the Oracle Java Cloud Service (OJCS) you get your own - prebuilt - Oracle Virtual Machine on an ExaLogic server. This OVM contains one or more WebLogic 11g servers. Just like the database counterpart, this environment will be (almost) instantly available and easy to use and manage. When signing up, or later, you can associate your OJCS with an Oracle Database Cloud Service or a Fusion Application Cloud Service. There will be a pre-built integration with Fusion Apps.

When signing up for one or more of these services, you can have a onetime free trial for 30 days. After that trial period (or immediately if you like), you will be charged per month for the services you`re signed up for. Both the Java and the Oracle Cloud Service comes in small, medium and large - and it is possible to up or downgrade. According to Oracle the pricing will be "competitive" - whatever that may mean...
Before this all goes live, there will be an Early Access period.

Apart from the Cloud Services itself, also a lot of tools will be "cloud enabled". There will be cloud add-ons for Eclipse and JDeveloper, Enterprise Manager will get a Cloud Control feature and SQL Developer 3.1 will have cloud support as well for up- and downloading data (using REST web services or using the new "Data Pump for the cloud".

So who`s this all for? I think the small version of the services could be very interesting for setting up a development environment within minutes. No need to order hard- an software when starting a project, or to reserve your space in the "private cloud" of your own company.
The medium and large versions are targeted at test or production systems. But with the current size limit, only small and medium businesses - or isolated departmental applications, can use this. And therefore it`s not only a competitor for Google and Amazon, but also for the smaller hosting companies. But that will be dependent on the price...

More information an sign up - when the time is there - on cloud.oracle.com!

Location:Ellis St,San Francisco,United States

New APEX feature regarding RESTful web services

noreply@blogger.com (Roel Hartman) — Tue, 04 Oct 2011 20:31:00 +0000

The upcoming Application Express version 4.2 - date not announced yet - will (or might), amongst other features regarding mobile support, also contain the functionality for managing the Apex Listener Resource Templates. From the SQL Workshop you can access the RESTful web services. And so create new or manage existing services of the APEX Listener. So therefore keep all stuff within one IDE.
Very nice. It will enhance the use of the Resource Templates enormously!

Location:Howard St,San Francisco,United States

Know your code : Automate PL/SQL standard enforcement

noreply@blogger.com (Roel Hartman) — Tue, 04 Oct 2011 00:14:00 +0000

This is a report of Lewis Cunningham's session at OOW11 on the subject t mentioned above.
For code you not only need coding and naming standards, but performance and testing standards as well. And of course you need to check wether your code complies with your standards. For analysis you can either do static or dynamic analysis (and instrumentation as well). For static analysis you can use the data dictionary, PL/Scope and the source code itself. For dynamic analysis you have to use profilers.
For retrieving the data dictionary you can query the _SOURCE, _DEPENDENCIES, _PROCEDURES and related views. When using PL/Scope you have to recompile the code with a "identifier:all" setting switched on. Then the results are retrievable from the _IDENTIFIERS view.
Analysis should be done at the earliest stage, that is during coding. PL/Scope can be very handy for validating naming conventions, impact analysis and identifying scoping issues.
For dynamic analysis, you have to set a baseline first. Save the timings for that baseline and check that after you made changes. Code coverage is also useful to detect pieces of dead code. You can get this by subtracting the dynamic, executed, code from the static, available, code. Alas code coverage is not 100%, because PL/Scope doesn't detect SQL.
All this stuff should and can be automated....
All details are in the Expert PL/SQL book.

Location:Howard St,San Francisco,United States

OOW2011 - Announcing Oracle`s Big Data Appliance

noreply@blogger.com (Roel Hartman) — Mon, 03 Oct 2011 16:27:00 +0000

During the Oracle ACE Director briefing, Mark Townsend, VP Database Product Management, did "State of the Union" on the Oracle database. This post is my report of his talk...
At this moment around 55% of the installed base of the Oracle database is on 11.2. Last year, Oracle made more money from selling more database licenses and more options on existing installations.
Mark mentioned that there are customers with 1,000`s of databases (and even one with 80,000!) - all using different versions of the database, of the operating system, storage etc. This situation is very hard to maintain and to keep up and running. Inn Oracle`s view, consolidation into a "private cloud" is the solution, and therefore Oracle offers Exadata. One (or less) databases are easier to secure, easier to make high available and easier to upgrade. And when you use Oracle software troughout your application stack, why not use Oracle hardware as well? So Oracle is striving towards a "red stack" (i.e. all Oracle).

The latest version of the Oracle database is 11.2.0.3. 11.2.0.4 is planned for somewhere next year. After that, Oracle 12 will replace the "g" with a "c" - for "cloud" of course! - and should be available somewhere next year as well. Oracle 12c is not a subject on this OpenWorld. You can sign up for the beta test, which will start in November.
Last week, the Oracle Database Appliance (ODA) was announced. The ODA comes with standard 24 cores, but you can license per core - completely different from the current licensing where you have to pay for all cores that are available in your hardware. In Oracle`s terminology, an "Appliance" is engineered for simplicity, anything called "Exa-whatever" is engineered for speed. Next to the ODA, Oracle announced this Monday the "Big Data Appliance", using a (new) Oracle NoSQL database (based on the Berkeley DB). This appliance will do massively parallel batch processing with Hadoop. Therefore Oracle will distribute Hadoop (and support it as well). There will be an Oracle Data Integrator (ODI) to get the data from Hadoop into a relational Oracle database. Another new product in this appliance is "Oracle R". "R" is open source replacement for SAS - a statistical tool for data-analysts (like the software used by the female computer wizard in the tv-series Criminal Minds). So the BDA consists of this whole stack (as I understood it). The BDA solution (or framework or architecture) is aimed at processing huge bulks of no-SQL data (key-value pairs), like user clicks on website, phone calls etc, but is good for oldfashioned ETL too!

Location:Ellis St,San Francisco,United States

OOW2011 - Announcing Oracle NoSQL

noreply@blogger.com (Roel Hartman) — Mon, 03 Oct 2011 16:21:00 +0000

NoSQL databases have already been around for a long time. Even Oracle owns one: Berkeley DB. Other well known databases are Voldemort, MongoDB and Cassandra.
A NoSQL database contains only key-value pairs and targets on only simple operations: store and retrieve data. Any relationships and other rules should be enforced by the application itself. A NoSQL databases has a small footprint, is embeddable, (very) fast, scalable and easy to use and usually runs on a lot of operating systems.
Therefore the sweet spot of NoSQL databases is processing loads of simple and unstructured data, like messaging, queueing and user web clicks. Not surprising that the big social networks, like LinkedIn, Facebook, Google and Amazon are heavy users of NoSQL databases. For some more advanced use some NoSQL databases have options for concurrency, transactional processing and high availability. Of course you can store this kind of data in a relational database, like the "regular" Oracle database as well, but that comes with a much higher price tag. An Oracle database can do so much more than just store data, but even if you don`t need those features, you still have to pay for them...
This Monday Oracle announced their knew Big Data Appliance in order to acquire, organize and analyze large volumes of simple, unstructured data in ann easy way. Part of this appliance is the new Oracle NoSQL database, which is - surprise, surprise - based on Berkeley DB. But, unlike most competitors, an Oracle NoSQL has, next to a C++ and Java API, also a SQL API! So NoSQL doesn`t mean no SQL at all, but Not Only SQL...
Oracle NoSQL will be available in two versions: a Community Edition which is free and open source and an Enterprise Edition. The functionality is the same, there is only a difference in the licensing... I am very curious how this will land in the, usually very independent and open source minded, NoSQL world!
More info on Oracle OTN

Location:Ellis St,San Francisco,United States

OOW2011 - Announcing the Exalytics machine

noreply@blogger.com (Roel Hartman) — Mon, 03 Oct 2011 01:45:00 +0000

After a long rerun about Exadata, Exalogic and the Supercluster, during Sunday`s keynote, Larry finally announced the new Exalytics machine. Extreme speed, due to 1 TB DRAM (holding 5 to 19 TB of compressed data) and 40 cores of Xeon CPUs. It is using a new version of TimesTen - the in memory database - and/or an new version of Essbase (for OLAP) and a new OBIEE. It not only handles relational and multidimensional data, but also "unstructured" data. You should connect the Exalytics machine to your Exadata machine with Infiniband. Then load (all) data into the Exalytics machine and start analyzing and processing in memory. It uses a "Heuristic Adaptive In-Memory Cache", so data changes are detected and refreshed in the machine. Oracle claims it is around 20 times faster than their current configurations.
Price tag? Not mentioned...

Location:Howard St,San Francisco,United States