APEX: Dynamic Action in Interactive Report

Yesterday I was showing a colleague how to use a Dynamic Action in an Interactive Report to do an update on the underlying table. To get this to work prior to APEX 4 you would need to write some javascript on the page as well as an Application Process. Mostly I used the method described by Roel Hartman a number of years ago.
Want to take a look at a demo first before the steps to take to make it work? It's right here.

ACED Nordic Tour 2012: 4 days, 4 countries

Can't believe it's almost two weeks ago.
Together with Mark Rittman, Carl Dudley, Sten Vesterli, and Tanel Poder, I was invited to join in for the ACED Nordic Tour.
During the Nordic Tour we visited Denmark, Sweden, Norway and Finland. Four countries in four days.
I knew it was going to be a busy week, but didn't expect to be exhausted when I got back home on Saturday.
On Monday I flew into Copenhagen, Denmark where I met up with Sten Vesterli and Ken Holmstykke Jensen from the Danish Oracle User Group. They were kind enough to show me around the city, Tivoli and the World Press Photo exhibit, all very enjoyable, thank you very much.

Tuesday was the day of the Danish Oracle User Group at the offices of Oracle Denmark. In Denmark I did two presentations: "Analytic Functions Revisited" and "Edition Based Redefinition". When all the sessions were done, and the beer started to come out, we had to go to the taxi and rush to the airport.

Wednesday was Stockholm in Sweden. The location was absolutely stunning, an old school building (so I was told), with grand staircases and high ceilings.
My session in Stockholm: "Who's Afraid of Analytic Functions?" and "Getting Started with APEX Plugin Development".
This time we really had to hurry to get to the airport, nearly losing Carl on the way the to the trains.

Thursday: Oslo. The board of the Oracle User Group Norway were kind enough to welcome us in the hotel with some food and drinks. A nice welcome after having had airport food for a few days. My sessions in Oslo: "Getting Started with APEX Plugin Development" and "Who's Afraid of Analytic Functions?"

Friday, the last day of the tour: Helsinki, Finland. The sessions that I did in Helsinki: "Getting Started with APEX Plugin Development" and "Solving Puzzles with SQL: Cheating when playing the game SET" . Even though my last name was spelled wrongly on the agenda, I can't really blame them - they are not the first. This time we didn't have to rush to the airport, at least not all of us. Mark and Tanel were flying out that day to go home.
Sten, Carl, and myself did stay an extra night in Helsinki so we could enjoy a very nice meal and some Finnish specialties (which all seem to include wodka).

Like I said before, on Saturday I was exhausted. Looking back at the tour I really enjoyed it. Wish I could see more of the countries than just the airport, but maybe some other time. Looking back at the tour, I really enjoyed it and was in good company.

Thanks to the user groups who invited us, and organizing the conferences. Special thanks to the Ace program for sponsoring.

OOW 2012: Little things make me happy

Yesterday evening we had the annual "We went to OOW and this is what's new in the world we call Oracle" at AMIS. Starting around 5pm and ending around 10pm a lot of information was presented by several AMIS colleagues.
My contribution to the evening was (amongst others) a little bit of new features in SQL and PL/SQL, only a little bit as there is too much to cover in just 20 minutes.
It's the little things that make me happy. What is the little thing that I like?
When you define a table you can assign a default value to a column (here it comes) based on a sequence. Take a look at the following (taken from the presentation Tom Kyte did during OOW):

First a sequence is created, with the resounding name S. Next the table (named T) is created where the column X has a default based on the NEXTVAL of the sequence S.
Just to repeat the line of interest:

x int default s.nextval primary key

How cool is that?
No longer will you need to create a trigger to fill the column with the nextvalue from a sequence, you just declare it this way and you're done.
Maybe it's just syntactic sugar, I don't know (yet) and frankly I don't care. I like the fact that this feature makes it more declarative to populate the primary key (or any other column of course).
I'm happy.

SQL Developer tip: Don't open the table

One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.
There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings:

Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click"

And that's it.

OOW 2012: Is PL/SQL still alive?

Yesterday Bryn Llewellyn, Distinguished Product Manager, did a session called "PL/SQL Enhancements brought by the latest generation of database technology". The room was packed. For the upcoming release of the Oracle 12c database the enhancements were broken into three areas:

• Improved PL/SQL and SQL interoperability

  In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.
  The types that can be used as bind variables has been extended: Boolean, Nested Tables can be used as bindvariable in Dynamic SQL.
  Subquery Factoring (the With clause if you will) can hold private functions, the use case presented for this: pretty print an integer in K, M, G, T.
  Using userdefined PL/SQL functions in a SQL statement is not advisable, but if you really need to there is a new pragma: PRAGMA UDF. With this pragma the statement is almost as fast as pure SQL.
  

• Security

  A new step has been taken to continue on "least privileges", meaning that the user only gets the privileges that are absolutely necessary. A Role can be granted to a PL/SQL unit.

• Improved programmer usability

  The ability to inspecting the callstack has been improved by the introduction of a new package: UTL_CALL_STACK. The package contains numerous functions to help in this area, such as current_edition and lexical_depth. White listing of program units: restrict which program unit can be called by other program units.

So, is PL/SQL still alive? YES! ALIVE AND KICKING!

OOW 2012: My Schedule

This will be my third time attending Oracle Open World and I'm really looking forward to the event. It is very hard to make a schedule, there are so many good session available. I thought I'd share my schedule, it might inspire you for your own schedule (or makes it harder to make a choice) and I can look at my schedule without having to login to the schedule builder.

APEX: Custom Image on Button Bug

When you use a custom template for your APEX application and you want to use an image on a button, you might encounter a small, trivial bug. Thanks to Anthony Rayner and twitter for a simple workaround. The Setting

For the custom theme that is used in the application that I'm working on, an Application Level Substitution string was created. You can define these substitution strings at the application level, available from the application home page.

Dutch preview event 'Collaborate 12'

'COLLABORATE 12: Technology and Applications Forum for the Oracle Community’ is de grootste Oracle gebruikersgroep conferentie ter wereld en wordt jaarlijks georganiseerd in de Verenigde Staten. Het is hét evenement om bij te zijn wanneer je geïnteresseerd bent niet alleen in Oracle Applications (E-Business Suite, JD Edwards, Siebel, etc.) maar ook in Oracle Technologie in het algemeen, van ontwikkeling tot beheer.
Ook dit jaar zal een aantal Nederlandse Oracle experts een presentatie geven op deze conferentie: Roel Hartman (Logica), Alex Nuijten (AMIS), Arian Stijf (Arven) en Arnoud Roth (Inter Access). Aangezien niet iedereen in de gelegenheid is om naar Las Vegas te gaan voor het bijwonen van deze conferentien, wordt er door Inter Access een Dutch Preview Event georganiseerd. En wel op woensdag 18 april vanaf 17 uur bij Interaccess in Hilversum, waar alle presentaties van deze experts zullen worden getoond. Van Application Development, E-Business Suite Security, SOA tot Oracle Fusion Applications, de onderwerpen zijn zo divers dat je zeker een interessante avond zult beleven.
Aanmelden voor deze preview kan per mail naar info@interaccess.nl. Meer informatie is verkrijgbaar bij Arnoud Roth, bereikbaar op telefoonnummer: 06-54363429
De sprekers en hun onderwerpen:

Alex Nuijten, AMIS

• Solving Puzzles with SQL: Cheating when playing the game SET
• Who’s Afraid of Analytic Functions
• Database Development: SQL Holmes - The Case of the Missing Performance

Roel Hartman, Logica

• 5 Cool Things You Can Do With HTML5
• APEX Boot Camp: Striving for Perfection: The Ultimate APEX Architecture
• Done in 60 Seconds – Creating Web 2.0 Applications Made Easy

Arian Stijf, Arven

• Securing the E-Business Suite. A Layered Approach
• From Workflow to BPEL

Arnoud Roth, Inter Access

• Oracle Fusion Applications Architecture Fundamentals

Linked-in event

ORA-03113 and Bulk Collect

It can be quite frustrating when you are working on a database package and your session is terminated time and time again and leaves you clueless as too why this is happening.

APEX: Show Actions menu with Authorization

With an Interactive Report you can choose whether you want the Actions Menu to be shown or not. This has to be done by the developer. Of course the customer wanted to have this based upon the existing authorization scheme. How can you solve this? Dynamic Actions to the rescue.

Comparing Schemas: Red Gate Schema Compare for Oracle

This week I attended a webinar by Cary Millsap, organized by Red Gate. It was called "Real Developers DO use Tools" and was focussed on, well, tools. At the end of Cary's talk, James Murtagh showed a demo of their Schema Compare tool. The demo wasn't flawless, but he recovered nicely :) (note to self: don't do live demo's). And this - the demo, not the occuring error - triggered a memory that I wanted to write a blog on the Schema Compare tool. If you missed the webinar you can find it on the website of Red Gate here: http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/webinars/webinar-archive I had the pleasure of meeting James Murtagh during the UKOUG annual conference at the end of last year. James also hosts the All Things Oracle site. While we were discussing different topics (even non-oracle) he asked me which tool is my "development tool of choice". The tool I use predominantly is PL/SQL Developer by Allround Automations. "We have a plugin for that",... time to take a closer look at the tool.

Upgrade Oracle XE from 10 to 11: A word of caution

Finally I had some time to upgrade my Oracle XE database from version 10 to version 11, something I wanted to try out but could never find the time to do it. I wanted to try it out, to see if it all worked as advertised, usually it does. This time however I ran into some problems with the export and import of the APEX applications, good thing it was just on my sandbox database.

APEX: Friendlier exception message from AJAX call

While it is very easy to create a "Form and a Report" on a single table (or view), just follow the wizard, for end users it is not always intuitive that they should navigate to the form page to remove the record. A nicer solution is the one described by Anthony Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a little trashcan on the report page.

Generate multiple rows

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so.