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.

Upgrading to APEX 4.0: Templates

In the last weeks I migrated more applications from APEX 3.x to 4.0 and thought to share my thoughts on the Theme and Templates part of the application.

I've applications that were initially build in APEX (HTMLDB) v2 and other build straight with APEX 3.x. Some applications used a build-in theme, but more had a custom look and feel.

Most migrations went ok, but I did have issues when I used templates which didn't have all tokens (#...# things in html that get replaced at run time by APEX) e.g. with tabular forms (but I'll do a specific blog post on that - and is already a known issue).

I'm personally following the path of installing a new APEX 4.0 theme for my application and apply changes to that to get the same look and feel I had before. Or at least I verify my custom templates to support the new APEX 4.0 features.
I also try to update it to a nicer and more up-to-date look and feel.

The reason why I'm following this approach are multiple:

• better w3c compliant
• easier for screen readers (for disabled people)
  
• fresher and more up-to-date look and feel
• support for all new APEX 4.0 features
• more div/css based layout, which makes it more clean and easier to adapt in the future

But obviously you should decide yourself if it's worth updating your templates in your applications ...

APEX Meetup @ OOW 2010

It's an annual tradition, so also this year we'll organize an APEX Meetup at the 4th Street Bar & Deli on the Tuesday (7.30 PM) during Oracle Open World.

Here you find some pictures of last year. As you can see, it's always fun with nice people, drinks and food.

Every year we get more people during the meetup, so we started to get sponsors to help to pay for the bills ;-)

Thanks so much to our sponsors of this year:
- Oracle Technology Network (OTN)
- Oracle Development Tools User Group (ODTUG)
- APEX Development Team

If you or your company wants to sponsor aswell, feel free to send me a mail or add a comment to this post. The current total to spend is $500 :-)

See you soon!

Follow-up on comparing two Oracle schemas

Over 4 years ago I wrote a blog post about software that helps you to compare two Oracle schemas. That post is accessed a lot and I still get questions about it, so I decided to write a follow up on that post as things change over time.

Comparing two Oracle schemas is still something I have to do regularly, e.g. if I want to compare a development, test and production instance after a deployment of an application.

Alongside Oracle Application Express (APEX), I use Oracle SQL Developer daily. They have a built-in Database Diff tool which works, but I tend to use other tools as well, as it’s hard with SQL Developer to see exactly what has changed. We built our own tool in APEX that compares schemas based on dbms_metadata, which we use when we have access only through the APEX interface. But when I can run things from my own system, I like to use Schema Compare for Oracle.

The people of Red Gate wrote a nice post on how to use their software, with screenshots and steps you can follow, so I won't repeat that. Instead I’m going to compare it against the Diff Tool built into SQL Developer.

Simplicity

The older I get, the more I can appreciate user-friendly software. That is something I try to do in the software I write myself, follow the KISS principle = keep it simple stupid.

For me software has to look good, ’be easy to use and do what it’s intended to do in an efficient way. If I want to compare two schemas I just want to follow a couple of steps:
1. Select my source and target database
2. Select the schema(s) I want to compare
3. Optional - select what exactly it should compare (tables, packages etc.)
4. Get an overview of the results
5. Produce synchronization scripts

Schema Compare for Oracle (SCfO)

Here’s how it works in Schema Compare for Oracle.

When you open Schema Compare you have to create or open a project.


It asks you for the source and target database you want to connect to and which schema you want to compare. An advantage in SCfO is that you can compare multiple schemas at once.
Another thing I found interesting was the option to compare against a Snapshot. You basically take a “picture” of your schema at a certain moment in time and compare against that. Very handy when you can’t access both schemas from the same location. Or if you develop applications and want to generate upgrade scripts, I see the use of snapshots too.

But let’s get further with the normal schema compare.

In SCfO you don’t select the objects first, you just hit the Compare Now button and SCfO starts to compare the whole schema directly. At first I thought, I don’t really want you to do that as you are doing a lot of work for nothing, but the more I used the tool, the more I appreciated it. To start a comparison is very easy and quick...

One thing I found handy as well is the Options tab, which allows you to define if the tool needs to ignore white spaces, storage clauses etc.


When we are happy with the options, we click the Compare Now button and we get a screen with the progress.


Once it’s finished you have a complete overview of the differences in both schemas.


I like this screen a lot as it has many more functionalities than you would first imagine. The filter (find box) is very handy to find some specific objects quickly. You can also sort by different things and just from the way it looks it is so easy to understand what is different in which schema and database! But the nicest thing is when you click on a row where there are differences. It shows you both versions and highlights the differences. No need for an extra tool or text editor to get that information out. It’s just there.


If you want to create an upgrade script or make both schemas equal you just have to use the Synchronization Wizard...

The wizard asks you what it has to do and generates a script for you or automatically synchronizes both schemas based on the objects you selected.


I think Schema Compare for Oracle does it really well. So let’s compare it to the comparison tools that come with Oracle SQL Developer.

Oracle SQL Developer

In SQL Developer you go to Tools - Database Diff.

The first time it will tell you it’s using Oracle Change Management, a payable option of the Oracle database, and you have to acknowledge you have a proper license to use that.


Next, it will pop up with a screen that allows you to select a source and destination connection and immediately asks you what objects you want to compare.


Clicking on Next shows you a screen where you can select the individual objects.
You can view all objects at once, or change the select-list to only see tables, sequences and select the objects that way. This might be a good thing to do if you have many objects!


Once you click Finish it will compare the objects and present you the result in a Diff Report.


You can click on the green SQL button and it will generate the script for you based on the differences you selected.

So it’s rather straightforward to compare two Oracle schemas in SQL Developer, but there are some things I would prefer a bit different.

This is my wish list:
1. Not be forced to have the Change Management license for your Oracle database. The price depends your Oracle Database license, but it can be high if you just want to compare two schemas through SQL Developer.
2. Be able to compare multiple schemas at the same time.
3. Get a cleaner Diff Report, which allows me to add filters to it and allows me to see what exactly is different between the two versions. At the moment I just see a count there, when I click on the row. I get the script to generate that version, but I couldn’t find a way to see exactly the source and target and compare them side-by-side.

Point 3 is my biggest issue and that is why I searched for something else that made my life easier and finally came across Schema Compare for Oracle. Although Red Gate created a Windows only version of Schema Compare for Oracle, so as a Mac user I need to run it through a VM, I find it worthwhile to do it. The interface of SCfO is very clean, it’s easy to use and it gives me most features I’m searching for. There is even a SQL Developer plugin for SCfO (search for Schema Compare for Oracle in the SQL Developer Plugin repository).

So is Schema Compare perfect and is it better in every aspect than the Diff Tool in SQL Developer? Almost, but not completely. SQL Developer supports the comparison of Materialized Views (and their logs) and Database links, which SCfO doesn’t do in the release I tested (1.3).The version of SQL Developer I used was 2.1. I’m not sure the Diff Tool in SQL Developer 3 (which will be released in a few weeks/months) will be different...

Happy comparison!

APEX 4.0.1 Patch released

Hi just read Joel Kallman's post that the Oracle Application Express (APEX) team released 4.0.1 which corrects a number of issues found in APEX 4.0.

I recommend going to 4.0.1 if you have 4.0 installed as soon as possible.

If you still have 3.x running and want to upgrade to 4.0 you can directly download the 4.0.1 release and install that.

The new version of APEX is 4.0.1.00.03.

Add Default to Tabular Form in APEX

If you want to set a default value for a column in your tabular form, you can set the Default Type to "PL/SQL Expression or Function" and put into Default e.g. 'N'.


That sets the default for that column to the static text 'N'.

Upgrade to APEX 4.0 - Known Issues - Workarounds and Patches

If you upgrade your application from APEX 3.x to 4.0 you might encounter some issues.

At ODTUG I did a live migration of an application written in APEX 3.2 to 4.0 and I talked about some of the issues I discovered and how I worked around them. I'm doing the same presentation at APEXPosed in Brussels in October. You also might have seen my previous posts where I upgrade DG Tournament from APEX 3 to 4.

On the Known Issues page you see the issues categorized nicely. If the issues can be fixed by working around it, it's also mentioned there. Oracle is working hard on a patchset (4.0.1) and they hope to release it soon. On Metalink you can already find patches for specific issues too.

I plan to write some other blog posts with steps I did when upgrading DG Tournament to APEX 4.0.

APEX Father is speaking ...

If you didn't pickup another important APEX blog to follow yet... you definitely want to follow Mike Hichwa.

Mike is the "father" of APEX, it must be over 10 years ago he started with the development of APEX (HTMLDB/Project Flows).

Mike is a VP at Oracle and is now responsible for a lot more than APEX alone. He also has SQL Developer and many other tools all related to Database Development.

ODTUG comes to Europe in October! Registration open!

As announced on the latest OBUG APEX SIG, we will participate in the first ever ODTUG event in Europe!

It's a two day conference focused on PL/SQL and APEX, with two full tracks for each.

If you look at the Agenda you will see the presenters are well known in the APEX community and amongst the best in their field. On the PL/SQL side you have ofcourse Steven Feuerstein and a lot of other big names.

Registration is open now, so don't wait to register, places are limited!

My first time at Orcan - and wearing a kimono!

A few months ago I got an invitation from the Swedish Oracle User Group (Orcan) to present at their event, which I agreed to do. It was my first time in Sweden and it was an unbelievable experience. The environment is so nice there... a lot of green (trees), a lot of water, nice people...

About the event itself, it started all normal. My first presentation was "Mastering an APEX Page", which you might have seen me doing before. I basically explained how an APEX page works and how/where to change things so you get exactly what you want.

But this event was in a SPA hotel... a very relaxing environment with hot tubs, steam baths etc. and everybody was wearing a kimono! My second presentation I did in such a thing! It was very strange in the beginning to be wearing a kimono, but soon it became very nice and comfortable. I talked about my experiences going from APEX 3.x to 4.0 and demoed a lot of the new features in 4.0.

The event was also a great time to meet other people I didn't meet before but follow on internet. On the picture you might recognize Francisco Munoz Alvarez, Daniel Morgan, Husnu Sensoy, Grant Ronald and Jose Senegacnik. We all had a great time with some nice food.


If you've never been to Sweden or one of the events of Orcan, it's definitely worth it.

Thanks to Patrik Norlander and his team to make this an unbelievable experience.