ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

If you get an ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. from your DML process when you submit the changes in an Oracle APEX application, then this is because Oracle APEX tries to lock the effected record before it updates/deletes it. This behavior was introduced in Oracle APEX 3.0, but with the side effect that complex updateable views with an instead of trigger get the above error.

BTW, why does Oracle not have a "locking" branch in an instead of trigger???

Back to the topic, what can we do?

In Oracle APEX 3.0.1.00.07/08 a new switch was added to disable that behavior, it's documented in the readme for the patch, but not in the online help (bug has been filed). The switch is called FSP_DML_LOCK_ROW and is a substitution value or an application/page item. If you set the value to FALSE, the locking will not be done by the DML process.

But is it a good idea to disable locking for the entire application?

I think No! It should only be done for those pages where it's necessary.

1. Create an application item named FSP_DML_LOCK_ROW
2. Create an application computation with the following settings
   • Sequence: 0
   • Computation Point: After Submit
   • Computation Type: Static Assignment
   • Computation: TRUE
3. Create a computation on the pages which have the error
   • Sequence: 10
   • Computation Point: After Submit
   • Computation Type: Static Assignment
   • Computation: FALSE

BTW, there is another switch which effects the locking, it's called APEX_DML_LOCK_WAIT_TIME. For details have a look at the documentation.

« ... Read full posting ... »

Do you want to learn more about Oracle APEX and get some insights into Oracle APEX 3.2?

Then you have to attend our second Oracle APEX training! This time it's held in Munich from 02-Jun-2008 to 04-Jun-2008.

More detailed information follows in German:

Denes Kubicek, Dietmar Aust und ich freuen uns Euch wieder ein paar neue Einsichten, Tipps und Tricks zu Oracle Application Express (APEX) vermitteln zu dürfen. Die Agenda der Themen welche wir behandeln gibt es hier.

Besonders freut es mich, dass wir dieses Mal den Marc Swetz vom Oracle APEX Entwicklungsteam aus den USA als Gastredner begrüssen dürfen!

Marc wird uns ein paar Einblicke in die nächste Version von Oracle APEX und dem SQL Developer geben. Er wird auch an den Abendlichen Q & A Sessions teilnehmen, damit er aus erster Hand die Fragen und Anregungen die bei der täglichen APEX Entwicklung auftauchen, aufnehmen kann. Nutzt diese ideale Gelegenheit um Euch mal mit dem Oracle APEX Entwicklungsteam auszutauschen! Und keine Angst, der Marc spricht Deutsch :-)

Die Abendlichen Q & A Sessions sind überhaupt beim ersten Training sehr gut angekommen, weil es die Gelegenheit ist seine konkreten Projektfragen/-Probleme von drei Oracle APEX Experten beantworten zu lassen.

Also nicht lange zögern sondern jetzt Anmelden!

Würde mich freuen Euch dort zu sehen.

« ... Read full posting ... »

Undocumented option for Static List of Values

Did you know that the STATIC and STATIC2 keyword used to define Static List of Values for Oracle Application Express (APEX) contains an undocumented option?

The online help documents the usage with

STATIC[2]:Display Value[;Return Value],Display Value[;Return Value]

But what to do if one of your values contains the semi colon or the comma in the text? You will get a problem with the predefined separators!

In such a case you can use

STATIC[2](lov-entries-sep,display-return-sep):Display Value[<display-return-sep>Return Value]
<lov-entries-sep>Display Value[<display-return-sep>Return Value]

For example:

STATIC2(~,*):Cat, Dog*1~Nemo, Shark*2

« ... Read full posting ... »

Is there life on planet Oracle APEX 3.2?

Sure there is! ;-)

David Peake the Product Manager for Oracle Application Express (APEX) recently blogged about this "unofficial" insight what's on the drawing board after the patch release for Oracle APEX 3.1.

Get the details in this A peek over the Application Express horizon posting!

« ... Read full posting ... »

Remote debugging of an Oracle APEX application

If you ever wanted to remote debug the PL/SQL code which is called during execution of an Oracle APEX application, then have a look at the May/June issue of the Oracle Magazine. David Peake wrote an article titled The Power of Two on how to use SQL Developer to do that. On the German Oracle APEX Community Web-Site you will find a similar how-to in German.

Update: In Oracle APEX 3.1.1 there will be a new debug mode called REMOTE to enable remove debugging in Oracle APEX without having to modify the application. See the related posting on the OTN Forum.

« ... Read full posting ... »

Oracle APEX: Got a 404 Not Found?

Maybe you also already got the 404 Not Found error message

The requested URL /pls/apex/wwv_flow.accept was not found on this server.

when you submit an Oracle APEX page, ...

There are a lot of reasons why this error can occur. But the above error message doesn't tell you a lot about the root cause of the problem. The real error message can be found on your Apache Web server in the Apache\Apache\logs\error_log.xxxxx file. There you will find and entry like

[Thu Apr  3 ...] mod_plsql: /pls/htmldb/wwv_flow.accept HTTP-404
wwv_flow.accept: SIGNATURE (parameter names) MISMATCH
VARIABLES IN FORM NOT IN PROCEDURE: P_T02XXX
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM:

Without the above information, you have no chance to find out what's causing the 404 error. But the problem is, that most developers don't have access to the application server.

You don't have to!

Just put

<Location /pls/apex>
 ...
 PlsqlErrorStyle  DebugStyle
 ...

into your dads.conf/marvel.conf and after restarting the Apache server you will get the error message directly in your browser.

For more details about PlsqlErrorStyle, have a look at the documentation.

Note: Only use this setting on developments systems, because on production systems a hacker might get more information as he should!

Credit for this tip goes to Dietmar Aust!

« ... Read full posting ... »

Free Oracle APEX seminar in Denmark

I just read on Peter Lorenzen blog that his company Logica and Oracle are hosting a free full day Oracle APEX seminar on 23-Apr-2008 in Copenhagen, Denmark.

Anthony Rayner from Oracle UK will also be there and speak about what is coming in release 4.0.

So if you live in that area, get the details at Peter's blog posting.

« ... Read full posting ... »

Caution: Apex_Item.Date_Popup and Oracle APEX 3.1

Just to let you know in case you are hitting the following problem or plan to upgrade to 3.1. The version of Apex_Item.Date_Popup which comes with Oracle APEX 3.1.0.00.32 has a "small" bug. The Date Picker will always write the return value into the first row, independent for which row you have opened the Date Picker.

Carl has already provided a workaround for the bug and I'm pretty sure it will be fixed in the first patchset of Oracle APEX 3.1.

« ... Read full posting ... »

I'm back!

As you may have already noticed, I'm back from my journey to Vietnam and Cambodia. Was a great vacation, liked it alot!

I'm already back since two weeks, but had not much time for blogging or other things. After coming back I almost immediately had to fly to Luxembourg for a project startup meeting of a new Oracle APEX project my company is doing and the nights where used to prepare material for the Advanced Oracle APEX training I did with Dietmar Aust and Denes Kubicek a week later. It paid off to put that effort into it, because we got great feedback from the attendees. More on Dietmar's blog.

So I hope to find now some time to work on some task which have queued up over the last few weeks. Like a new release of the APEX Builder Plugin (the training attendees liked this tool alot when I showed it to them), a new ApexLib release (it's really getting time) and I finally should make the switchover from Blogger to my own hosted Wordpress installation. Not to forget to write some new blog postings, ... So there is a lot to do in the upcoming weeks. :-)

« ... Read full posting ... »

Using Windows Login Credentials for Single Sign On (NTLM authentication)

Most users are lacy and don't want to enter login credentials each time they start up an Oracle APEX application. They tell you,

"Hey I have already logged in into my computer, can't you use that information for the APEX application?".

Sure we can! The NTLM protocol is one way to do that.

Instructions on how to integrate mod_ntlm have been around for quite some time. See the article on HTMLDB Studio and a more up-to-date version by Martin Köb of GreenIT. But that always involves installing mod_ntlm on your Apache.

Jason Straub, a member of the Oracle APEX development team did some digging and found a PL/SQL only solution which doesn't require mod_ntlm. Have a look at his first blog posting called "NTLM HTTP Authentication and Application Express" and welcome him as new member of the Oracle APEX blogging community!

Jason, welcome and keep blogging such great articles!

« ... Read full posting ... »