APEXBlogs v2 - APEX Usage

This post is part of the 10 days of APEXBlogs. This series of blog posts highlight the features of the new version (APEXBlogs v2) of APEXBlogs.info which will be released shortly.

So far I blogged about

1. Backend changes
2. Showing the blog posts APEX 4.0 style
3. Twitter sync for #apexblogs and #orclapex
4. Plugins section
5. Links
6. APEX Usage (this post)

As I travel quite a lot and speak to different people, I get the impression that APEX is more and more used in all parts of the world. So I would find it very interesting which part of the world is using Oracle Application Express a lot and which parts of the world are just adopting it.

It's very hard to get these numbers, maybe Oracle themselves have an idea if they look at the statistics of apex.oracle.com.

I thought a long time about a way to get to that info, but there is no easy answer really:

• I could look at the IP's that hit my sites and by that I can figure out from which country they are (e.g. by using IP2Location website)
• I could ask people to create a profile/login on the APEXBlogs v2 site, but why would they do that?
• I could mine other sites like Facebook, Linkedin, Blogs, Google, the APEX Forum etc. to see which people are talking about APEX or asking questions about it, but that would be technically the hardest job to do and I wouldn't find everybody that way either
• I could manually enter numbers, based on my talks at conferences and other persons feedback
• I could use Google Analytics (which is actually using the IP based solution) to see from which countries people come

My absolute dream would be that I would get APEXBlogs v2 in such a state that it's worthwhile for everybody dealing with APEX wanting to go to that site. We'll see in the future how that turns out, but you could definitely help me with that. I see APEXBlogs v2 evolving to more a portal page for the APEX Community... v2 is not anymore about just aggregating the blogs, it's doing a lot more as you could read before and will read in the next couple of posts.

Once we are online with APEXBlogs v2, the way you could help is talking about the site and tell everybody it would be fantastic to get the APEX adoption in card. You are probably as much a fan of APEX as me, but you might find it difficult to convince others to go with APEX and do more APEX projects. By showing that APEX is used all over the place, it might help to convince your client(s) and/or boss.

The other way you could help is to give me feedback about what would be useful for you and the people you know, so they have a reason to come to the site.

So back to the APEX Usage and how it will be shown in APEXBlogs v2.

I wanted to use a combination of two methods. My first routine looks at the people who are registered and plots that on a World Map (implemented and works great). In APEX 4.0 there's a wizard to create these maps. The second routine will look at the IP addresses that hit the site and verify if the numbers are aligned from the first routine. But I've an issue here... if I'm with a client and hit the site I've a certain IP address, when I'm at home I've another IP address, but I'm the same person. Or behind one IP address (e.g. a government) there might be 100 people using APEX.

I doubted to use a Google Map and show the points where people exactly are, but that would probably have the reverse affect and people don't want (or are not allowed) to share that information (privacy reasons).

APEX Patch 4.0.2 released

Just a quick note to say that there's a new patch for Oracle Application Express that fixes a number of issues and also adds some enhancements to APEX (e.g. two new themes, API additions to APEX_UTIL etc.)

To download and read more about the patch set, Joel Kallman from the APEX Development team wrote a good blog post about it.

Thumbs up for the APEX Development team for giving us these nice additions and the fixes.

Oracle Application Express (APEX) 4.1 and beyond

The APEX Development team updated the Statement of Direction for the future releases of Oracle Application Express.

As we can read on that page, Oracle Application Express 4.1 will focus on enhancement to existing functionality and additional capabilities to support applications running on mobile devices. Application Express 4.1 is planned to incorporate the following:

• Development for Mobile Applications – Include themes and HTML templates suitable for smart phones and mobile devices.
• Charting - Provide for chart rendering without using Flash (to enable display on mobile devices).
• Error Handling - Improve error handling and user-defined exception processing.
• Interactive Reporting – Allow multiple reports on one page and support pivot queries.
• Tabular Forms – Allow multiple tabular forms on one page and continue to expand tabular forms validations.
• Master-Detail-Detail – Allow the generation of pages to support master-detail-detail relationships.
• Dynamic Actions – Enhance conditional processing and allow dynamic actions to be defined for tabular forms, reports, and buttons.
• Plug-Ins - Add plug-in support for additional components and enhance plug-in definitions.
• Use of ROWID – Allow usage of ROWID for Automatic DML processing (as an alternative to identifying the PK columns).
• Modal Dialogue - Add ability to display a dialog on top of a page (the rest of the page will be grayed out).
• Websheets – Allow for greater control over user interface, new page section types, and enhanced spreadsheet / datagrid integration.
• Data Upload - Enable end-users to upload data into an existing table (within an application).
• Accessibility – Improve accessibility in existing themes and HTML templates.
• Numerous functional and performance improvements.

Great features I would say! For many of the above items we work around them with some hand written coding. As many of you know, I'm a heavy supporter of charts and dashboards and we've already been working on a non-Flash based charting engine for the current release of APEX.

This solution I actually showed at Oracle Open World too. We plan to release these non-Flash based charts soon too, so if you are on APEX 4.0 and you don't want to wait till 4.1 or later, no worries, something is coming ;-)

APEXBlogs v2 - Links

The Links section as it currently stands on apexblogs.info I moved off the homepage. In APEXBlogs v2 it will be an entire new page which holds an Interactive Report with all the Links I think are useful for people wanting information about Oracle Application Express.

As it's an Interactive Report, you can use the Group By functionality (new in APEX 4.0) to see the number of links by type. I created that report as the alternative view.

As you can see in the screenshot I started to capture links to Blogs (obviously), Oracle User Group APEX SIGs, APEX Examples and General APEX Links.

APEXBlogs v2 - Plugins section

I'll have to push back the release of APEXBlogs version 2 for some time. Most of the development is completed, but I'm still working on the new UI of the site. Before going live I also want to do some blog posts about what will be in this release, so if some of you have comments I can still consider these and possibly make changes. There are at least five other blog posts waiting, before the go-live...

As I wrote before, it's not just an update of the current APEXBlogs, instead it's a complete new release written from the ground up with many new sections/features.

One of the new sections in APEXBlogs v2 is the APEX 4 Plugins section. Aggregating blogs and tweets is fine, but in the APEX 4 world, plugins became very important, so I definitely wanted to keep up with what people wrote. So this new section of APEXBlogs is exactly doing that. It searches for the APEX 4 Plugins out there and aggregates them and provides you with an easy search (Interactive Report).

At this stage it aggregates the Oracle Plugins page and the Community Plugins rss feed. I decided to keep it like that for now and am not including other personal links to APEX plugins, but might consider to do that in the future if I see they don't get posted in the ones I aggregate now.

Technically it was interesting to get the Oracle Plugins as they don't provide an RSS feed of their plugins, so I parse the complete html page (with utl_html and regular expressions). I hope Oracle (Patrick) doesn't start to change the look and feel and the way they organized the page too many times ;-)

As you might see in the screenshot, I added a Rating column... I thought to review the plugins I aggregate and add my personal rating against them and some comments why. Obviously it will be a personal rating and might be subjective. On the community plugins page (apex-plugin.com) people can rate the plugins too, but the rating is not part of the RSS feed so I can't show that.

APEXBlogs v2 - Twitter sync for #apexblogs and #orclapex

Another new feature of APEXBlogs v2 will be the synchronisation of Tweets with the hashtags of #apexblogs and/or #orclapex.

Some of you might remember I enabled the synchronisation of Tweets with the hashtag of #apexblogs already in version 1, but I had to take it out as my procedure was sometimes hanging. My procedure/query would work in 95% of the times, but if Twitter was down, it would hang and the process would stay there forever. As Twitter was not that stable in the earlier days it caused to much of an issue, that was the reason it disappeared after a few weeks.

But now it's there again and better than before! Again I use the same new APEX 4 features as with the blogs; namely the detail view of the Interactive Report to show the tweets exactly like I want.

Here's a screenshot:

For the people interested behind the query to retrieve a Twitter stream, you can use something like this:

select *
from xmltable(xmlnamespaces('http://www.w3.org/2005/Atom' as "AE"), '//AE:entry'
passing httpuritype('http://search.twitter.com/search.atom?q=apexblogs').getxml()
columns title varchar2(250) path '/AE:entry/AE:title/text()'
);


That returns something like:

The above query would be easiest, but it won't always work. It depends the version of your database, your security settings and your environment.

Update:

Forgot to add that the twitter stream in APEXBlogs v2 is not realtime anymore. I'm going to sync every hour and will put a timer on the page till the next sync.

I choose to store the tweets in my own tables to have an archive, to increase performance and to enable easier search options through the Interactive Report.

APEXBlogs v2 - Showing the blog posts APEX 4.0 style

The main reason for APEXBlogs was to aggregate the blog posts and be able to search in an easy manner.

The way it was initially implemented was through a normal (classic report) in APEX and a search bar which allowed you to search for any text. Next you had the possibility to search between dates or on specific blogs.

To improve the standard functionalities of APEX 3.x I added some jQuery. E.g. clicking on the icon of "Blog Selection" would slide open the checkboxes with the blogs. Another example is the use of the jQuery date picker instead of the standard date picker.

This is how the search and blog view looked in version APEXBlogs v1.

APEXBlogs version 2 is written completely from scratch in APEX 4.0 and tries to use as much functionalities of APEX 4.0. So the above way of searching and the use of a classic report got replaced by an Interactive Report.

This is how the report view of the Interactive Report looks like.

This is how the detail view of the Interactive Report looks like (new in APEX 4.0).

As you can see, that view is similar to the classic report view I had in version 1.

The big difference is that it's just an Interactive Report, so all functionality of the Interactive Report you can still use! Adding filters, group by, doing highlighting etc.

You can just switch between the two views.

If you didn't know this functionality existed, you find it in the Report Attributes of your Interactive Report. There is a Detail View section which you have to enable and then you can add the html code you want it to look like.

Very neat feature of the Interactive Reports and it became very useful with building this new version of APEXBlogs.

Pictures of APEXPosed 2010 Brussels

Last week the first European APEXPosed event took place. It was a very successful event with over 120 people spread over the APEX and PL/SQL sessions. It was the first time ODTUG came to Europe and it was kind of special for me as it was in my own country.

Although it took some time to organise (special thanks to Kathleen and her team) and also during the event we still had a lot to do, I'm so happy everybody seemed to enjoy the event.

I believe the sessions where of a very high quality and the overall atmosphere was great (thanks to all the attendees!).

Below you find some pictures of the event (thanks to Patrick and Kathleen to share it with me).

Here you see Anthony presenting...

During the reception on the first day we had some good laughs as well. It was a great opportunity to talk to all the people and have some drinks together. On the picture from left to right: Patrick, John, Roel and me.

After the reception some of us went out to have some food. They thought I knew Brussels well, which is only partially true ;-) We were trying to get a place into the best two restaurants of Brussels but they were both fully booked, so we ended up in a Portuguese restaurant.

The food was good, but the company was even better ;-)

The last day we had the quiz. It was great fun! John did an excellent job in reading out the questions and staying serious at the same time as some questions were hilarious :-)

The winners of the quiz got an Amazon gift card and obviously had to be photographed...

First place: Peter

Draw for the second place: Edgar and Niels

Third place: Roel

Steven Feuerstein also gave us some books to handout to the people that didn't make it in the top 3: Carsten, Marco and Maurize

If you couldn't make this one, we are doing another event in the first quarter of 2012!

APEXBlogs v2 - Backend changes

This post is part of the The 10 days of APEXBlogs.

Lets start with some things you don't see of APEXBlogs; the code that is pulling all the information from the blogs and aggregating it together.

This is the flow how it worked in version 1 of APEXBlogs (currently still used):

APEXBlogs itself is just the UI, in the backend you have packages (using XMLDB features) that connect to all the blogs and searches for changes. If it finds changes it will merge these changes in the tables APEXBlogs is build on. There are a couple of issues with this method:

• The more blogs you have, the slower it gets to look for changes as it needs to connect to the blog, read, search for changes, disconnect, connect to the next blog, read, search for changes etc.
• It not only became slower, it also used a lot of resources (CPU and memory)
• There are different kinds of blogs e.g. WordPress, Blogger (Blogspot), WindowsLive etc. You would expect the RSS format is universal, but it isn't, so I ended up with different code for the different kinds of blogs.
• The package was quite sophisticated as it could recognise the type of blog, but I got into trouble when people started to use their own urls (.com)

So I did a complete redesign of the backend code and the flow in version 2 looks now like this:

In version 2 of APEXBlogs there's only one connection necessary to update all the blogs at once.

The reason is because Google Reader is in the middle now. Google Reader is basically doing the hard work. I setup a specific Google Reader account for APEXBlogs which now holds the blogs to aggregate. Now I just access the Google Reader API to retrieve the posts and search for changes and I merge that stream into the backend tables.

Where the synchronisation of APEXBlogs version 1 took a couple of minutes, the synchronisation in version 2 is done in a couple of seconds. Also the database resources used and the network traffic are decreased a lot. My code is a lot simpler as I only need to maintain one code base, the one for Google Reader.

So now you know how things work behind the scenes... tomorrow I'll focus on how I show the blogs in APEXBlogs v2.

OBUG Benelux Connect 2011 - Call for Presentations

On March 29, 2011 the Oracle Benelux User Group (OBUG) will hold its 4th OBUG Benelux Connect.

As in previous years, this event is being organized in close collaboration with Oracle.
Possibly you were one of the 700 visitors who attended the 2010 event at the Papendal Conference Centre in Arnhem, The Netherlands in March earlier this year and you are eager to present your customer case at the upcoming OBUG Benelux Connect 2011.

OBUG Benelux 2011 will be held at SQUARE Brussels Meeting Centre in Brussels, Belgium. Square is housed in an elegant, architecturally significant building originally constructed for the 1958 World Expo.

The theme for OBUG Benelux Connect 2011 is "The Real User Experience".

The strength of OBUG Benelux Connect is the customer cases presented by OBUG members and other Oracle customers. Learn from peers to maximise the value of your business-critical applications and technology.
Speakers will share their experiences and business solutions with you in more than 40 sessions scheduled in different streams: Applications (Oracle eBS, Peoplesoft, Siebel, JD Edwards, Primavera), Technology (DBMS, JDeveloper/ADF, etc.), Middleware (BPEL, SOA, EAS, etc.) & Business Intelligence (Hyperion, OBI, GRC, etc.). There will also be a complete APEX track.

CALL FOR PRESENTATIONS

End-users, project leaders, implementers, IT and functional managers and CIO´s share your experience with Oracle products in a 45-minute presentation – (slides to be prepared in
English, presentations to be given preferably in English). You can submit your abstract here.

The 10 days of APEXBlogs

As in analogy with "The 14th Days of jQuery" I plan to write the next 10 days a blog post about the new features in APEXBlogs v2 (release planned for November 12th).

For the people that don't know APEXBlogs yet; I created that site for a couple of reasons:
- Give the APEX community an easy access to the most interesting blog posts about APEX
- Backup the content of the blogs, so even when a blog would disappear, the info around APEX would not be lost
- Have links to the other interesting sites about Oracle Application Express
- To promote APEX
- Show what you can do in APEX. The initial release of APEXBlogs was written in April 2008, at that time APEX 3.0/3.1 and showed the integration of jQuery and other 3rd party components.

An older screenshot of APEXBlogs:


I leave the current APEXBlogs site running, but am not doing any development or maintenance on it anymore. That means some links are out of date or some blogs don't get synced correctly. I'm sorry for that, but I decided to put my energy in the new version.

I started version 2 of APEXBlogs a couple of months ago and decided to build it again from scratch in APEX 4.0. Version 2 is working in parallel for some time now and is syncing the blog posts, so any blog post written as we speak will appear in this new version when it goes live.

I'm very excited about this new version of APEXBlogs, but at the same time I've a lot of competing priorities, so lets see what will make it in the release of the 12th of November ;-)

Final day to submit your abstract for ODTUG 2011

There is only one day left to submit your presentation for the ODTUG conference... so you have to be quick to think about your title and abstract.

ODTUG is a great conference to meet and share ideas with other people using APEX. I always enjoy viewing others people presentations.


If you want to have your hour of fame, don't hesitate to submit!

SQL Developer 3 EA new features

Last week seemed to be the week of the releases of the SQL Developer team. After Oracle Data Modeler 3.0 early in the week, it was the turn to SQL Developer at the end of the week.


Version 3.0 of Oracle SQL Developer has many new or enhanced features
e.g.

1. DBMS Scheduler
2. DBA Functionality
3. Files and Version Control
4. Migrations
5. PDF
6. PL/SQL Support
7. Query Builder
8. Schema Browser
9. SQL Plus Commands
10. Spatial
11. Tuning
12. Unit Testing
13. Unloading and Uploading

As you see some very cool and useful features. In some of our projects we use Spatial and Locator and the Map Viewer will become handy there. Having a DBA pane to get more info about the DBA is great too, so you don't have to write your own statements (scripts) or use EM.

I tried the Query Builder, but that didn't seem to work for me. It took a long time to search for the objects, but finally I just got a blank window. I could add other tables from other schema's, but the schema I was connected to was not included. Maybe I'm doing something wrong or it's because of the EA state and it's not optimal yet, not sure. I didn't really go into much detail to find the real cause as I've other priorities at the moment.

Next to the list of new features at the oracle website, it's also worthwhile to follow Kris Rice's, Sue Harper's and Barry Mcgillin's blogs as they blogged (or will be blogging) about some of the new features and included some demos. They are actually in the team that build this wonderful tool!

Data Compare for Oracle

Just a quick note to say the people at Redgate have a public release of Data Compare for Oracle now. If you ever want to compare your data, this might be an option for you.

They are the same people who build Schema Compare for Oracle which I reviewed some time ago.

Oracle Data Modeler 3.0

I just read that SQL Developer Data Modeler is out (renamed to Oracle Data Modeler).

The Early Adopter release for 3.0 can be found here.

Also check Kris Rice's blog as he's doing some nice blog posts about this new version and the features that come with it.

Oh... and if you didn't know yet, Oracle Data Modeler is FREE now :-)

APEX 4.0 Tree

In APEX 4.0 there's a new Tree Region. It's really cool and a lot better than the previous tree in APEX 3.x.

When you navigate throughout this tree there is no submit anymore, it also looks a lot better.

The only catch is that the query is not that straight forward.
If you look under the query, you'll see the explanation what columns the query should have etc. Be careful as the example under need is missing the level column!

Anyway, check out the tree, it's definitely something that people seem to like.

APEXposed 2010 Brussels

It's only 4 weeks away, then there is APEXposed! It's a two day (October 27-28) Seriously Practical Training. This conference is special as it's actual two conferences in one. People registering for APEXposed can also attend the sessions from OPP (the PL/SQL conference in the same hotel, same date, with Steven Feuerstein).

It's the first time ODTUG is coming to Europe and they want to make it a success. They teamed up with the local European Oracle User Groups. As it's in Belgium there's a strong collaboration with OBUG (Oracle Benelux User Group). With the OBUG APEX SIG we already had a first event in June, so APEXposed will be the second event where the OBUG APEX SIG will be present.

If you look at the agenda for APEXposed in Brussels, it's unbelievable... the best speakers and most known names in Europe will present. A big part of the APEX Development will be there as well.

But that is not all... if you are a member of OBUG, you get $100 off the already low price! (You'll have to use the OBUG discount code)

Space is limited, so I recommend registering as soon as possible. If you need to spend the night, click here to reserve your room. ODTUG has reserved a block of rooms at a special conference rate.

Mike for President (ODTUG)

This is a post to help my friend Mike Riley to be reelected as the President of ODTUG (Oracle Development Tools User Group). It's not so much because I like him, it's because I think he deserves it.

During the last two years Mike made ODTUG even more successful than they were before. With his kindness, his easiness to communicate and always with a smile on his face he makes us all feel really appreciated and welcome at ODTUG and the Kaleidoscope conference.
In the last years, Mike was a real believer in APEX and made sure we had a dedicated track for that. Obviously it was not him alone doing all the work, there were many other people, but it's under his president ship the tracks really grew to what they are now (the most APEX presentations and highest quality of any conference).

I included a picture we took at OOW a week ago (thanks Joel for taking it!), so you can see for yourself how he looks like if you didn't know him yet (Mike's on the right) ;-)


This year there are 14 people trying to get elected and there are only 4 places!
So, if you still need to vote for the ODTUG Board Elections, don't forget Mike...

OOW - finished

I'm heading back to Belgium at the moment... It was a very busy week at OOW but I enjoyed it very much. I still plan to do some more blogging once I'm back.

In the meantime I would appreciate people having pictures of the APEX Meetup or other APEX sessions putting a link on this post... normally I'm the guy doing a lot of pictures, but I forgot my camera this time...

The only picture I've so far of the APEX Meetup is the one taken by Joel, where I stand with Jason (right).

Boarding now...

OOW - Saturday

Today I had a more relaxing day in San Francisco. After having done some work in the morning I went out with John and Rich. We had some nice food in the most famous Chinese place in San Francisco "House of Nanking". We went to that place for the first time in 2007, when Carl B. recommended it and took us there. Again the food was excellent.

When we walked back to the hotel, there was an event going on in China town. On the picture you see Rich and me with a girl everybody was taking pictures of.

Later that afternoon we had some coffee and nice and long chats. Time goes fast in good company...

ACE Director Briefing and start OOW

It's Saturday today and I'm currently in the Hilton hotel on O'Farrell Street in San Francisco.

Today there are no special plans, it's a day between the Oracle ACE Directors briefing of the last two days and the start of Oracle Open World (OOW) tomorrow.

Every year just before OOW, Oracle plans a briefing to the Oracle ACE Directors where people like Thomas Kurian (Executive Vice President of Product Development), Ted Farrell, Mark Townsend and other product manager tell us what's going to be announced at OOW and what the plans are in the future. I find it a real privilege to make part of this and appreciate the openness of Oracle. We all had to sign a NDA so are not allowed to speak about it.

Most of these things will be announced on Sunday and during the rest of the week at OOW, so a little more patience ;-) The only thing I can say is that the announcements won't be that much in the area I normally blog about, nevertheless the announcements are worthwhile! Something to look forward to...

One thing that is already public, is that Oracle SQL Developer Data Modeler is FREE from now on!

Tomorrow and the days after that the madness begins. After the acquisition of Sun, this event is even bigger as JavaOne is also at OOW. It will be interesting to meet all these new people.

I'm doing 3 presentations at OOW this year and one panel discussion. My first presentation is already on Sunday where I'll speak about upgrading your APEX 3.x application to APEX 4.0. I'll do a live demo where I take an export of an APEX 3.2 application (DG Tournament) and import that in APEX 4.0 and show the things that got changed or needs updated and I'll talk about the new features you can leverage now that you're in APEX 4.0.

Stay tuned ...

Upgrade to APEX 4.0 - Tabular Forms

It was a long time ago I promised this post, so finally today I found some time to finish the post.

In APEX 4.0 the Tabular Forms got enhanced quiet a lot. To make use of these enhancements it requires some manual changes of the page the tabular form is on, so when you upgrade your existing APEX 3.x (or older) applications the tabular forms are NOT upgraded automatically (or at least not completely), but they should still work like they did before.


The enhancement I want to discuss in this post is Adding a row to the tabular form.

Before APEX 4.0 when you hit the Add Row button, the page got submitted and a specific Add Rows process on the page got called.


In APEX 4.0 that changed: the page is not submitted anymore. When you hit the Add Row button, some javascript (addRow()) is called to add a new row to the tabular form.
So to make use of this enhancement, you need to make three changes:

1. Change the button to not Submit the page anymore, instead it should "Redirect to Url" with as Url target: javascript:addRow();
2. Delete the Apply MRU after submit processes (with the condition linked to the Add Row button): as the page is not submitted anymore when you click the Add Rows button it is not necessary to have that process anymore (obsolete)
   
3. Delete the Add Rows after submit processes (obsolete)

To identify the pages you have to change manually I use this query:

select WORKSPACE, APPLICATION_ID, APPLICATION_NAME, PAGE_ID, PAGE_NAME, PROCESS_NAME
from APEX_APPLICATION_PAGE_PROC
where PROCESS_TYPE in ('Multi Row Update','Add rows to tabular form')

It results in the below output and shows which pages you still have these processes on:


Another thing I found while upgrading my applications to APEX 4.0 related to tabular forms: if you still used an old template lay-out, the new functionality didn't work (missing of some tokens) and some other things which got already fixed in the 4.0.1 patch (see patch notes).

PL/SQL Developer on OSX

Running on Mac OSX is great and although most of the time I have a native Mac OSX app I can use, it sometimes happens I want to run a Windows-only program e.g. Internet Explorer (just to see if my app works in this browser!), BI Publisher plug in for MS Word, PL/SQL Developer or Schema Compare for Oracle.

So the options are running a Virtual Machine (I use VMWare Fusion) or use a program like CrossOver (Windows emulator based on Wine). Not all applications work with CrossOver, but it's worth trying to get it working there as it's more lightweight than firing up a VM.

In this blog post I'll show how to install PL/SQL Developer in CrossOver.

Once CrossOver is installed, you click Configure - Install Software.


Select Other Application and in Select an installer search for plsqldev804.exe file (which you can download from the PL/SQL Developer website. Finally hit the Proceed button to do the install. You just follow the wizard from PL/SQL Developer to finalize the install.

Once that is done we'll have to install the Oracle client so we can connect with PL/SQL Developer to our Oracle database. Easiest will be to download the Instant Oracle Client for Microsoft Windows (32-bit). Unzip the file and copy the directory to your C-drive. To open the C-drive: in the CrossOver menu click on Manage Bottles - Advanced Tab - Open C: drive in Finder.


Next you'll have to set the Environment Variables, so PL/SQL Developer knows where it can find the Oracle Client. Edit the cxbottle.conf file which you find in C:\

Add in the [EnvironmentVariables] section (depending your db characterset):

ORACLE_HOME=c:\instantclient_11_2
TNS_ADMIN=c:\instantclient_11_2
NLS_LANG=AMERICAN_AMERICA.UTF8

Next create a tnsnames.ora file with the connections to your database e.g.:

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCL)))

and copy that file into c:\instantclient_11_2 (or the directory you unzipped the Oracle instant client)

That's it... if you now start PL/SQL Developer (CrossOver - Programs - PL/SQL Developer), it will look like it's a native OSX app.


I had to do one more thing to get it complete like on a real Windows system. The icons in PL/SQL Developer looked a bit funny (gray/black - not really readable).
To solve that, go to PL/SQL Developer - Tools - Preferences - User Interface and uncheck Faded disabled buttons.

Fast writing of Queries with Joins in SQL Developer

When you create a report in APEX and build the source of that SQL query with the wizards you get these annoying quotes (") around the column and table names. It also takes time to do it with the wizard, time I don't have.

I tend to write my queries in SQL Developer so I can immediately fine-tune the query so it's exactly what I need. Writing your queries in a tool like SQL Developer has many more advantages, as you can look back in the history of your statements, you can immediately do an Explain Plan and format the code but even more immortantly it's very fast to write statements.

Let's say you need to write a query that joins four tables, you can just write it manually... Or your could just select the tables from the tree and drag them on your SQL Worksheet. SQL Developer will ask if it needs to join the tables for you and write the statement. It uses aliases for the tables, which is also something I like better than using the full table names in front of the columns...


Remark: you need foreign keys in place so the joins are written correctly.

As I saw many people didn't seem to know about that, I thought to quickly write something as it's definitely a time saver.

APEX 4.0: Application Utilities

I tend to use the Grouping functionality to logically group my APEX pages.
E.g. I've a group called 'Admin' which hold all the Admin pages.

I find it easy to see the pages for that section of the application and I can verify if my security model is fine on these pages. E.g. the pages should be protected with the Admin Role.
It also helps me to separate the work between developers. If I tell my colleague, go and change the Admin section, it's easy to find for that person.

You can also use ranges of pages for the different sections of the application.
E.g. 10000-20000 is for Admin pages.

Before APEX 4.0, the Grouping of Pages was in the Task area on the right hand side of the page, but that got changed. Now you find a lot of the Goodies in "Application Utilities" (see red arrow in the quick pick icons).

Clicking on that link will give you access to the Application Dashboard, the Upgrade Application wizard and many other features. On that page on the right hand side you'll see Page Specific Utilities.

Clicking on the "Cross Page Utilities" link will give you the links for grouping pages or bulk editing them (a feature I use a lot too, to rename my pages for ex.).


So the grouping feature is a bit more tucked away in APEX 4.0, but then you get a more logical grouping of features you can do.

SQL: Start day and End day of week

I just got an interesting question: how do you know for a date, in which week (Monday - Sunday) it falls.

I created a little test script to get 31 days of data:
(date_c is the same as date_d, but in a more readable output. c stands for char, d for date)

select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
   (sysdate + level) as date_d
from dual
connect by level <= 31 

Next I used the NEXT_DAY function to get the first Monday after that date, and the same for the Sunday. As it will give you the results of the next week I had to subtract 7 days so I was sure it would fall under the week the date was in.
e.g. if you have a date Wednesday 8 of September, that person wanted to have Monday 6th-Sunday 12th. If I just did NEXT_DAY of 8-SEP I would get 13-SEP, that is why I first had to subtract 7 to go to a previous week and get the next Monday.
For the end date it's similar, but there you only have to subtract 1 day, as only the Sunday might be a problem, as the next Sunday is the next week.

So the final query became:

with t1 as (
select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
    (sysdate + level) as date_d
from dual
connect by level <= 31 )
 select date_c,
        next_day(trunc(date_d)-7,'Monday') as start_day_of_week,
        next_day(trunc(date_d)-1,'Sunday') as end_day_of_week
  from t1 

A more readable screenshot:


Update (after comments of Kees and Alex): the following solution looks even more elegant and is NLS independent:

with t1 as (
select to_char(sysdate + level, 'Day DD-MON-YYYY HH24:MI') as date_c,
    (sysdate + level) as date_d
from dual
connect by level <= 31 )
 select date_c,
        trunc(date_d,'iw') as start_day_of_week,
        trunc(date_d+7,'iw')-1 as end_day_of_week
  from t1 

Upgrading to APEX 4.0: jQuery

In a lot of my APEX 3.x projects I used jQuery to extend the standard functionalities of APEX with some more interactive (client-side) components.

In jQuery there are very nice plug-ins, like a modal window, a grid, tooltips etc.

If you try to run your APEX 3.x application in APEX 4.0 you might find these jQuery components/plug-ins are not working anymore. The cause can be multiple:

• You might have used an older version of jQuery and the plug-ins you used with that, which are not compatible with the jQuery version in APEX (1.4)
• You load jQuery, but APEX loads their build-in jQuery too and things get mixed up

During the migration of my APEX 3.x application to APEX 4.0 I just take off all references to jQuery I made myself. Just make sure that in your template the #HEAD# is before any other jQuery plug-ins you try to run. Also make sure the jQuery plug-ins you use are compatible with jQuery 1.4.2. APEX ships with a lot of extra jQuery plug-ins like jQuery UI, js-tree etc so if you used that it's not necessary anymore to include them. (to see a complete list of files that ship with APEX, look into /images/libraries/)

Before I loaded my files as part of my Page Template, but I'm not sure I would still do that.
If you switch from template you lose the references. It might be better for maintainability point of view to include it in a region on Page 0. It will have a small performance hit, as APEX will have to look for it on Page 0, but it makes maintainability easier.
But going forward I'm converting all my custom javascript and jQuery plug-ins to APEX plug-ins and the files get loaded as part of the plug-ins. A lot of the javascript can disappear as it can be replaced by the build-in Dynamic Actions of APEX 4.0.

Hope it can help some of you when you see javascript errors when you try to run your APEX 3.x app in APEX 4.0. But don't panic to fast, it's probably just the way jQuery gets loaded.