Roels Blog

How attending #Kscope13 can change your life ...

noreply@blogger.com (Roel Hartman) — Sun, 28 Apr 2013 18:21:00 +0000

Wow. Doesn't that sound dramatic? It sure does. But in a weird way it also might come true - as I have experienced in the past.

The first Kscope conference I attended was five years ago. And purely by coincidence that was in New Orleans as well! That first year I met all the people I knew from the forums, blogs etc. I had never met one of them before in real life. But due to the relatively small size of the conference and the specific open "Kscope culture" (you can just step up to anyone and start talking!), getting in touch with whoever you like is quite easy. So contacting the "guru's" - as I saw those people back then, I know better know; now they're all "just" friends - has never been easier!

And what did that bring me?

After that Kscope I focussed more on sharing my knowledge by blogging, answering questions o the OTN Forum and presenting.
After a few years Oracle rewarded my efforts with an ACE award (and I got an assignment as ACE Director some time later).
I wrote a chapter in the Expert Oracle Application Express book - all the authors I have met at ... Kscope.
I wrote (the major part) of the APEX for Mobile Web Applications book - what all started by someone I met at ... Kscope.
Currently I run the Dutch branch of the APEX Evangelists company. The two founders I met at ... Kscope.

Thus without Kscope, I would have never

started sharing my knowledge in the way I did since then.
become an Oracle ACE (or ACE Director).
written a chapter in an "Expert" book - and certainly not a whole book!
started a company of my own.

In short, attending Kscope did have an enormous effect on my business and personal life. And obviously for the better!

So if you have never attended a Kscope conference before (or if you have!), come to New Orleans and join us! But be aware it might change your life ....

^{(picture from http://diane-millsap.blogspot.nl/)}

Mark your calendars: May 14 ODTUG's Expert Panel on APEX !

noreply@blogger.com (Roel Hartman) — Wed, 24 Apr 2013 10:01:00 +0000

On May 14, the second online ODTUG's Expert Panel will take place.

This time it's all about Oracle Application Express!!

So tune in on May 14, at 12:00 EDT (that's 5PM in the UK, 6PM CET).

The panelists are: Dietmar Aust, Dan McGhan and myself.

Patrick Cimolini will act as the moderator.

During this session you can ask any APEX related question to one of the three (or all three) panelists. And that can be either via chat or by voice - like a talk-radio show...

You can register here.

Oh, and did i mention it was FREE ??

Virtually see you there!

Roel

New book: Oracle Application Express for Mobile Web Applications

noreply@blogger.com (Roel Hartman) — Tue, 16 Apr 2013 08:22:00 +0000

Very recently a new book came out: "Oracle Application Express for Mobile Web Applications". This book is aimed at developers with some knowledge of APEX and want to know more about building web applications with APEX (of course) especially aimed at mobile devices (iPhone, iPad, and other brands of smartphones and tablets). And although I had promised myself never to get myself involved in writing another book after "Expert Oracle Application Express" (because it is sooo much work), I did get lured into writing this one by Dan McGhan.

Alas, due to personel circumstances, Dan had to step out of this project, but this vacancy has been filled in more than adequate by Christian Rokitta and David Peake.

Although I didn't receive my own author copies yet, David was so kind to bring his ones over during last week's APEX World in The Netherlands. And here is the picture of the unofficial launch of the book.

Before I forget, you can order the book on Amazon... I hope you like it!

Help! APEX is blowing up our memory!

noreply@blogger.com (Roel Hartman) — Thu, 21 Feb 2013 18:45:00 +0000

That's the message I received from a DBA friend who ran into this problem at her customer site. What really happened was that a couple of times a week one of the APEX processes was killed by the operating system due to reaching the limit of 16Gb memory. 16Gb!

And this resulted in an ORA-04030 message in the Oracle Database.

So I went to that client site and we dove into the database log files, the APEX logging, the access log files and the error log files of the web server. Every time we noticed calls of WWW_FLOW_FILE_MGR.GET_FILE that seemed to be the problem. But the application wasn't huge, just 25 pages. Nothing complex, all pretty straight forward. So we started to look for the origin of those calls.
Just like in a lot of applications, this application also had a few images on the top of the screen (like Home, Logout, etc.). Just four or five on every page. However, these images were stored inside the APEX Repository, using the #WORKSPACE_IMAGES# substitution string. And that results in a call to WWW_FLOW_FILE_MGR.GET_FILE for every image. So that's five requests for every page, while it could - and should - be only one. And four executions of that procedure every time, while it could - and should - be zero. So I advised to move these images to the web server, so they can be cached and wouldn't result in hitting the database anymore.
But when this would be the cause of the problem, it made sense it would occur on peak times. But the problem didn't occur on peak times. The last one was that same morning around 4:30AM.
So we dove further and then we discovered that quite a few of the select statements in the application used #WORKSPACE_IMAGES# as well! So the procedure would be executed for every row returned by the select statement. So in some particular cases - just happening three times a week or so - the query would return 10,000 of rows. Or even millions. So the procedure would be executed that often. And the best thing is: the image that the select was trying to retrieve ... didn't even exists! So millions of calls to the database to retrieve ... nothing!
So changing the select statements and moving all images to the web server will reduce the number of calls to that procedure from millions (or 100 of millions) times a week to ... zero (?).
And the chances are the memory problem is gone to.

So when you run into these kind of problems were APEX seems to consume a lot of memory - or CPU for that matter - take a very good look to your own code first. The chances are that the resource consumption is not because APEX is misbehaving, but your code isn't optimal....

Changed rendering (and evaluation) behaviour in APEX 4.2

noreply@blogger.com (Roel Hartman) — Wed, 12 Dec 2012 19:23:00 +0000

Today we ran into a problem in our recently upgraded APEX environment. In one of the pages we had a report defined on a pipelined table function. This function also changed a package global variable. The value of that global variable is used as a source for a page item that shows up in the same region just below the report. This ran fine in 4.1, it runs fine when we set the "Compatibility Mode" to 4.1 as well. But if we set that mode to 4.2...the page item shows...nothing !!!

What happened? According to the help on that Compatibility Mode property, items (and regions) in a display point are evaluated before rendering that display point. This is done to find out whether items / regions are displayed or not - to support the grid layout. To dig a little deeper (thanks to Patrick Wolf, who was helping us with this issue): As soon as the region grid is calculated, the same is done for the page item grid in the region which gets painted next. This calculation also has to determine the page item values, because they could be used in a condition of one of the following page items. This is all done before the region source is actually executed.

So, in our case, the value of the page item - what was based on the result of the pipelined table function - was evaluated before that pipelined table function was actually executed...and therefore returned null. We could easily spot that in the debug output: When we compared the order of evaluations of the region and the items we saw a (huge) difference in 4.1 and 4.2 mode.

So now we know what caused the issue. But how to solve it?
We created a PL/SQL sub region to the reports region and moved the page item to that subregion. That was not enough to evaluate the page item value after running the report. So we removed the PL/SQL source of that page item and moved it to the source of it's PL/SQL region. Even that didn't work out. But when we set the Item Display Position to "Below Content", we - finally - saw that the item was evaluated and rendered in the right position of the complete page flow....

So when you notice - similar - strange behaviour when running your upgraded pages in 4.2, it is worthwhile to check the difference in evaluation and rendering order is the cause of that!

Looking forward to UKOUG 2012

noreply@blogger.com (Roel Hartman) — Sun, 25 Nov 2012 10:47:00 +0000

As every year, the UKOUG conference is planned in the first week of December - and therefore as always nicely clashes with a Dutch national event, called "Sinterklaas" (see http://en.wikipedia.org/wiki/Sinterklaas for the background information on that event!). Luckily my kids are somewhat older now (and "non-believers"), so we can plan our own private event in the weekend afterwards....

Nevertheless, I am really looking forward to this years event! It will be my sixth consecutive attendance (and presentation), so I know what to expect: Great content, good atmosphere, lousy weather...

As usual I used the schedule planner (which has a great integrated App now as well) to guide me through these three days.

I am really looking forward to two sessions in particular (apart from my own at Monday afternoon): "Deploying and Developing Application Express with Oracle Pluggable Databases" by David Peake and "Oracle APEX - Websockets (or When Push Comes to Shove)" by John Scott - got raving reviews when he did this at KScope12...

And of course the social events are at least as important as the sessions: Sunday night's ACE Dinner in particular (thanks to Debra Lilley for organizing!).

OOW2012 - PL/SQL Enhancements in Oracle 12c

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

So what's planned for the upcoming 12c database regarding PL/SQL? What follows is a list of what are presented as the most important changes...

Improved PL/SQL - SQL interoperability

You don't need to specify the type at the schema level when you want to use it SQL, specification at the package level is sufficient and you can bind it to SQL directly;
Get the performance of SQL with the clarity and reusability of PL/SQL by adding a pragma UDF to a schema level PL/SQL function - almost as fast as pure SQL;
You can declare a PL/SQL function in the WITH clause of a subquery: with function x(param) <body> end x; select x(p) from t;
Using the "with" construct is 3.8x faster than using an old fashioned PL/SQL function, function with pragma is 3.9x faster, pure SQL is 5x faster.

A new security capability

Grant access to a PL/SQL unit by granting a role to a PL/SQL unit, where the role contains the required rights for accessing the tables - so a function can only select, update etc from a table where it has been granted access to.

Improved programmers usability

New package UTL_CALL_STACK for better display of errors (with functions like dynamic_depth(), unit_line(), subprogram(), concatenate_subprogram(), owner(), current_edition(), lexical_depth());
You can whitelist units that can reference a particular unit to prevent unwanted calls: add "accessible by (proc1, proc2)" to the unit definition.

OOW2012 - The Oracle Database 12c: The Pluggable Database

noreply@blogger.com (Roel Hartman) — Mon, 01 Oct 2012 16:26:00 +0000

To me, one of the biggest announcements this week was not that Larry finally learnt how to use a clicker, but the disclosure of some of the new features of the next version of the Oracle Database. This version is also the "first real multi-tenant database". So what does this mean?

In the current version of the database the core Oracle data dictionary is mixed up with the tables, packages etc you created yourself. All information is stored in the same obj$, tab$ and source$ tables. In 12c there is an architectural separation between the core Oracle system and your own application. The core Oracle data dictionary is called the Container Database (CDB). The part of the database that contains your own code is called the Pluggable Database (PDB). This PDB also contains its own datadictionary with it own obj$, tab$ etc tables. So it is a sort of hierarchy: objects are first located in the PDB, when they're not found there, information from the CDB is retrieved.

The best thing is, you can plugin many PDB's into one CDB. And so creating a true separation of data and sources between two databases that are plugged in into one CDB. It is obvious that both the CDB and the PDB have a much smaller footprint than a database containing both. So upgrading the Oracle software will be done on the CDB only and will be much faster. Cloning a PDB - containing your application - can be done way faster because you don't need to clone all the core Oracle stuff

Implementation under the covers is done using transportable tablespaces with data and metadata. This architecture also implies there are different types of users: local users in a PDB and a common user in the CDB. A PDB can be administered by a privileged local user, the metadata about the PDBs are administered by the common user. And any privileged user can switch between PDBs. Every PDB has a default service with its name. Service names across the CDB hav to be unique within the CDB. And you can define database links between PDBs.

You can imagine that creating a new, empty, PDB is 1000 times faster than creating a whole new database...

From an APEX point of view this implies that you actually can run multiple versions of APEX in one database - as long as every APEX version runs in a separate PDB!

OOW 2012 Schedule

noreply@blogger.com (Roel Hartman) — Thu, 27 Sep 2012 23:28:00 +0000

As always it is hard to create a schedule for Oracle Open World. Knowing that there are about 100 concurrent sessions, you'll miss 99% of all the content...

Nevertheless, I combined sessions around APEX, Database Development, Cloud etc into an interesting schedule for the upcoming week...spiced up with the social/networking events as usual.

So for what it's worth, here it is (and note my own session as almost the closing session of the event..):

Bye Logica. Hello APEX-Evangelists!

noreply@blogger.com (Roel Hartman) — Thu, 27 Sep 2012 18:31:00 +0000

17 years ago I started at working at CMG. 17 years! That's a long time, and a lot of things have changed during that period. CMG became LogicaCMG. LogicaCMG became Logica. And recently Logica merged with CGI - and the new name will probably be just CGI in a few months or years. So the company grow from a few thousand people mainly in The Netherlands and the UK to over 70,000 worldwide now. And you can imagine the structure of the organisation changed as well, from a team oriented approach to a more multi layered management structure. And, in my experience, meanwhile the culture of the organisation changed from a employee centred company to a shareholder (a.k.a. "money") centred organisation. So Logica as it is now, is a completely different company is the one I started 17 years ago.

So, a few months ago, that made me think: Would i spend the rest of my working life - what is still 20 years or so - at this company. And my answer was "No". So what's next? I could work for one of the competitors. but that wouldn't make a big change, wouldn't it? I might find a job at a "regular" company - not being some kind of IT integration company. That could be interesting, but I might find it somewhat boring after a few years. So what's another option? Starting a company for myself - or become independent is!

I talked with a some people that already made that decision earlier and they made me even more enthusiastic as I already was. I also talked to Dimitri and John from APEX Evangelists and there we found a match. APEX Evangelists was looking for a way to grow and saw The Netherlands as an interesting opportunity. So after some talks, emails, etc. we agreed I will start a Dutch branch of APEX Evangelists! And that's official as of today...

I am looking forward to expand the AE business in The Netherlands and work closely together with John and Dimitri. So, starting today, you can contact me at roel<at>apex-evangelists<dot>com...

APEXposed! 2012. Better than ever....

noreply@blogger.com (Roel Hartman) — Mon, 30 Jul 2012 14:54:00 +0000

No need to say more...impressive list of speakers, great city (I heard), and good fun! All other info and sign-up on http://odtugapextraining.com.

Handling errors in tabular forms - don't forget the commit...

noreply@blogger.com (Roel Hartman) — Wed, 11 Jul 2012 13:33:00 +0000

I always found manual tabular forms in APEX a sort of pain in the butt. I tried to avoid them whenever I could. But now and then there is no escape...

One reason why I dislike them is, when your submit process throws an error, your page is repainted again with all the fields in the original state. Sigh.. So you have to re-enter all your data and don't make that mistake again.

The standard solution is to fill a collection with all your data on page load, build your tabular form on top of that collection, save the entered values into the collection and finally save the contents of your collection (or the entered values themselves, as they are equal on this point) to the real tables. So I implemented that.

But to my suprise, it didn't work! Debugging, looking at session state, etc nothing helped. Until I got the luminous idea of putting a "commit;" in the process that saves the entered values into the collection. That helped a lot...

I always thought that all processes committed automagically, but in fact it seems that all processes (probably the ones that fire on the same process point) are committed all together. Or rollbacked for that matter...

Maybe this will help you prevent in making the same mistake!

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=22115: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=22115: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=22115: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=22115: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 ;-).