Roels Blog

Last week I presented at the OUG Scotland conference. To my surprise, I received the latest, 50th edition (!), of Oracle Scene - the publication of the UK OUG during registration. And, less to my surprise, there was an article in there written by yours truly: " Building Mobile Web Applications with Oracle Application Express ".  And when I got back home, I found the magazine of the Dutch Oracle User Group on the doorstep, with another article authored by me. Not really "another" as it is just a translated version (the English one was first...).  Nevertheless, quite proud of publishing not one, but even two articles in well known magazines in the same week!

Thanks to a tweet of Scott Wesley (@swesley_perth), I stumbled upon this blog post by Tony Andrews. In that blog Tony lists a few examples of APEX Conditions and concludes that the declarative approach must be the best way of doing it. Although his assumptions and conclusions makes a lot of sense, I wondered whether it was actually true and what the difference in performance would be. So I created a very similar test myself and take a look at the debug output. And here are the results (in microseconds) ... Type Statement Run 1 Run 2 Declarative ...<nothing here>... 653 667 PL/SQL Expression "...Execute Statement: begin wwv_flow.g_boolean := :P3_JOB_ID = 'AD_PRES'; end;” 1533 1823 SQL Exists ...Execute Statement: select count(*) from sys.dual where exists (SELECT NULL FROM DUAL WHERE :P3_JOB_ID = 'AD_PRES') 1684 2023 PL/SQL Function """...Execute Statement: declare function x return boolean is begin be...

One of my current customers would like to use their LDAP (Microsoft Active Directory) server for authentication and authorization of APEX applications. Of course we tried to set up a standard LDAP Authenication that's available within APEX. But we couldn't get that to work. Maybe it has to do with the fact that the client stored their Users within Groups within Groups within .... . Or maybe it doesn't do a full tree walk in the directory. Or maybe it is just because it is Microsoft - and not Oracle Internet Directory (OID). So we moved to a custom Authentication using the DBMS_LDAP functions (and some examples from the Pro Oracle Application Express book and Tim Hall - a.k.a. Oracle Base ). One of the issues we encountered that we wanted to use the user's login name, like "jdoe" and not his full name ("John Doe"). And the login name is stored in the "sAMAccountName" attribute. But authenticating using just "jdoe" didn't w...

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...

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

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 A...

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 strin...

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 determ...

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 Co...

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...

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 bes...

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..):

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...

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 .

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 ...

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.we...

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 wh...

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 onl...

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...

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.h...