Resetting pagination of a Master-Detail Report in Oracle APEX

After the "Oracle APEX round table talk with Mike Hichwa" I was asked an interesting question by another attending developer.

Is there a possibility to reset only the pagination of the detail report, when you have a master- and a detail report on the same page in an Oracle Application Express (APEX) application?

What is actually the problem with that? Why should the pagination of the detail report being reseted when a new master is selected?

Try out my example of a master-detail report and follow the steps in the instruction.

You will get an


"Invalid set of rows requested, the source data of the report has been modified." error when you do that and the reason is that Oracle APEX is not able to show a second page if there are not enough records for that new master selection. Independent from that it's always nicer, if the list starts from the beginning if a new master has been selected! :-)

I thought for sure it shouldn't be to hard to reset just a particular region. The "Reset pagination" which you can specify for a link/branch isn't really an option, because it resets pagination for all report regions - also for the master report, which isn't so cool if you end up at the first record set, when you select a new master...

So what to do? The Oracle APEX OTN forum is always a good source for such a problem, after a quick search I found an old thread from Vikas asking the same question. But as it looks like, no solution was found. I had to come up with my own solution.

So I looked what Oracle APEX is doing to paginate to a new record set, examined how the URL looks like and came up with the following solution.

When synchronizing the detail report, we have to "simulate" a pagination of the detail report to the first record set. We can do that by sending the region id of the report as REQUEST value and by attaching &pg_min_row=1 to the end of the URL.

Try out the working example.

These are the steps to include it into your own application.

1. Set the "Static ID" property for the detail report region to some unique value. (Skip that if you don't have Oracle APEX 3.0 yet)
2. Create a "Hidden" page item (eg. P3_REGION_ID) which is assigned to the region of the master report
3. As "Source Type" set "SQL Query"
4. As "Source value or expression" use the following query for Oracle APEX 3.0

   SELECT 'pg_R_'||TO_CHAR(REGION_ID)
     FROM APEX_APPLICATION_PAGE_REGIONS
    WHERE APPLICATION_ID = :APP_ID
      AND PAGE_ID        = :APP_PAGE_ID
      AND STATIC_ID      = 'Value which you have set for the detail report'

   
   or

   SELECT 'pg_R_'||TO_CHAR(REGION_ID)
     FROM APEX_APPLICATION_PAGE_REGIONS
    WHERE APPLICATION_ID = :APP_ID
      AND PAGE_ID        = :APP_PAGE_ID
      AND REGION_NAME    = 'Employees for department '||CHR(38)||'P3_DNAME.'

   
   for pre-Oracle APEX 3.0 installations. "Employees for ..." has to be replaced by the exact name of you report region.
5. In the master report, open the "Column Link" properties of you link column and insert &P3_REGION_ID. into the "Request" property.
6. For the last "Value" assignment, attach &pg_min_row=1 at the end (eg. #DNAME#&pg_min_row=1).
   

Why are we not just hard coding the pg_R_6721068803959845958 into the "Request" property? The reason is that this number can/will change if you have to import your application, that's why it's better so select the current ID from the Oracle APEX repository.

That's it! Have fun building master-detail reports!

« ... Read full posting ... »

Integrating Oracle APEX with PayPal Payments Pro

The Oracle APEX team has just published a white paper about Integrating Oracle Application Express with PayPal Payments Pro. They have also created a packaged application which you can use to immediately start of.

Good job Jason! There have been several postings on the OTN forum where people have been looking for such an how-to to integrate PayPal into there application.

« ... Read full posting ... »

Für Kurzentschlossene: Oracle APEX "Roundtable" mit Mike Hichwa

In German, because it's a local event.

Für alle Kurzentschlossenen, Oracle veranstaltet am 20.09.2007 in Hamburg einen

Oracle APEX "Rountable" Talk mit dem Gründer und "Godfather" Mike Hichwa

Aus der Mail von Oracle:

Die Idee ist, über Kundenprojekte und deren Wünsche und Ideen konkret zu diskutieren.

Wünschenswert wäre es, wenn Sie eines Ihrer APEX Projekte kurz vorstellen könnten und evtl. erläutern, an welchen Stellen Sie sich noch "Verbesserungen" wünschen. Dazu bräuchten wir vorab allerdings einen kurzen "Abstract", vielleicht 2-3 Screenshots und eine kurze Beschreibung der Anwendung; bitte senden Sie diese an edith.wild (at) oracle dot com

Natürlich sind Sie auch "ohne konkretes Projekt" willkommen und herzlich eingeladen mit zu diskutieren.

Weitere Details und das Anmeldeformular.

« ... Read full posting ... »

Oracle Application Express (APEX) 4.0 - Preview Video

Just read that Carl Backstrom has made his video public, which is showing on of the "WOW" features of the upcoming Oracle APEX 4.0 release. The video was first shown at the "First Oracle APEX Training Days" by APEX Evangelists. Side note: Dimitri Gielis and John Scott blogged about there experience doing this training. Check out there blogs. Good job guys!

But back to the video, check out Carl's posting APEX 4.0 Preview (aka Video Killed the Radio Star) to see him in "action"! :-)

I would suggest to get the higher definition version, so that you can see the real beauty of the new feature. BTW, you have to get the VCL player as Carl has written, because the video is coded with the Theora coded which Windows Media Player and QuickTime can't decode.

And now just a few screen captures as teaser...

New context menu for report columns



On the fly filtering



On the fly grouping



On the fly row/cell highlighting



So users will have a lot of "on the fly" options to modify the report you have created for an Oracle APEX application.

« ... Read full posting ... »

What is the foundation of Oracle Application Express (APEX)?

You are correct, it's PL/SQL! I'm sure you knew that ;-)

So that means every improvement which is done for PL/SQL is good for us Oracle APEX developers, like the function result cache, the continue statement and much more which have been introduced in Oracle 11g.

Did you know that there is a new initiative by Steven Feuerstein and the Apex Evangelists guys to bring new enhancement requests to the attention of the Oracle PL/SQL product management?

Check out the new "I love PL/SQL, and ..." web-site and help to improve PL/SQL!

« ... Read full posting ... »

Upcoming Events

Interested in learning more about Oracle Application Express (APEX)? Then you should attend one of the several conferences in the next few months.

September 23 - 26: SIOUG 07 by the Slovenian Oracle User Group (SIOUG)

Didn't know that the SIOUG is hosting such a big conference, according to the web site there will be about 500 to 750 people. I have to consider it for next year, because it isn't really far away from where I live (neighbor country). If I read it correctly (the content is in Slovenian) then there are two Oracle APEX presentations at that conference. See the conference schedule for details.

September 27: AOUG Jahrestagung 2007 by the Austrian Oracle User Group (AOUG)

It's just a small one day conference, but there are also two Oracle APEX presentations. See the conference schedule for details. If you like you can meet me there.

November 11 - 15: Oracle Open World 2007

There are a lot of Oracle APEX related presentations. According to a search I did I have found 13 presentations. I'm really looking forward to this conference. Hope to meet a lot of people I know from ODTUG and from the forum.

November 21 - 22: German Oracle User Conference by the German Oracle User Group (DOAG)

I have found 4 presentations on the schedule of this conference. One is done by me. It's called "Oracle APEX under the hood" and for which I have to write a summary this week. I haven't started yet... :-(

December 3 - 5: "many faces > one voice" by the UK Oracle User Group (UKOUG)

The agenda currently shows 13 presentations. Anthony Rayner blogged in detail about it. Check his blog for an overview of the Oracle APEX presentations.

« ... Read full posting ... »

Checking if a Page Item has been rendered or not

Did you ever have had the requirement that a validation should only fire if the page item has been rendered (eg. if you have defined a condition or authorization)?

As long as you set the "Associated Item" property in the validation to the page item you want to check, you are fine. Oracle Application Express (APEX) will do the check for you and only fire the validation if it's there. BTW, I didn't know that - really nice! :-) But if you create a page level validation it's up to you to determine if the item is there or not.

So you would probably go and duplicate your condition/authorization code to the page level validation. Not good! Twice the code to maintain.

You don't have to!

There is a nice tip posted on the OTN forum by Scott Spadafo which uses the Apex_application.g_arg_names array to determine if the item is there or not.

Attached you will find a function which you can use in a PL/SQL based condition (eg. PL/SQL expression). Call it with your page item name and it will return TRUE if the item has been rendered.

CREATE OR REPLACE FUNCTION isPageItemRendered
  ( pName IN VARCHAR2
  )
  RETURN BOOLEAN
IS
    vPageItemId NUMBER;
BEGIN
    ----------------------------------------------------------------------------
    -- Get the internal id of the page item.
    ----------------------------------------------------------------------------
    BEGIN
        SELECT ITEM_ID
          INTO vPageItemId
          FROM APEX_APPLICATION_PAGE_ITEMS
         WHERE APPLICATION_ID = WWV_Flow.g_flow_id
           AND PAGE_ID        = WWV_Flow.g_flow_step_id
           AND ITEM_NAME      = UPPER(pName)
        ;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Page item '||pName||' doesn''t exist!');
    END;
    ----------------------------------------------------------------------------
    -- If a page item is rendered the internal id is stored in a hidden field
    -- called p_arg_names. During submit the values are stored into the
    -- g_arg_names array by the WWV_Flow.accept procedure.
    -- By checking for existence of the page item id in the array, we are able
    -- to determine if APEX has rendered the item as "Saves state".
    -- Note: A item which is normally enterable but which is rendered
    --       "Read Only" is also considered rendered, because it still saves
    --       state.
    ----------------------------------------------------------------------------
    FOR ii IN 1 .. Apex_Application.g_arg_names.COUNT
    LOOP
        IF Apex_Application.g_arg_names(ii) = vPageItemId
        THEN
            RETURN TRUE;
        END IF;
    END LOOP;
    --
    RETURN FALSE;
    --
end isPageItemRendered;

« ... Read full posting ... »

Adding a CAPTCHA to your Oracle APEX application

Haven't had the requirement to add one in my applications until now, but after reading Dan's posting about integrating reCAPTCHA with Oracle SSO, I thought that shouldn't be to hard to integrate into an Oracle Application Express (APEX) application too! A nice challenge for the evening ;-)

I know that there are some similar solutions out there on the OTN forum and on the unOfficial Wiki page, but the SVG solution isn't very secure in my opinion and the other solution uses an unsupported non-public package of Oracle APEX. So how can we integrate the reCAPTCHA widget instead?

Just follow my step by step instruction and you are done in minutes!

1. Sign up at reCAPTCHA to get the private and public key.
2. Load the stored function into your application schema which you can find at the end of the article.
3. Go to the page where you want to integrate the CAPTCHA
4. Create three page items
   • Name: P<your page>_RECAPTCHA_CHALLENGE_FIELD, P<your page>_RECAPTCHA_RESPONSE_FIELD and P<your page>_RECAPTCHA_ERROR
   • Type: Hidden
   • Sequence: Should be before all other items
   • Item Source: Always Null
5. Put the following JavaScript code into the "Post Element Text" property of an existing page item or create a new region of type "HTML". Replace P7 by your own page number and also replace "your_public_key" with your public key you got during the registration.
   

   <script type="text/javascript"
      src="http://api.recaptcha.net/challenge?k=your_public_key&error=&P7_RECAPTCHA_ERROR.">
   </script>
   <script type="text/javascript">
   function renameRecaptchaField(pRecaptchaFieldName, pApexFieldName)
   {
     var vRecaptchaField = $x(pRecaptchaFieldName);
     var vApexField      = $x(pApexFieldName);
     // Set for the dynamically generated Recaptcha field the same name as for
     // our APEX field (eg. t02), so that wwv_flow.accept puts the value into the APEX field.
     // After that remove the APEX field, because wwv_flow.accept can't handle
     // two fields with the same name.
     vRecaptchaField.name = vApexField.name;
     vApexField.parentNode.removeChild(vApexField);
   };
   renameRecaptchaField('recaptcha_challenge_field', 'P7_RECAPTCHA_CHALLENGE_FIELD');
   renameRecaptchaField('recaptcha_response_field',  'P7_RECAPTCHA_RESPONSE_FIELD' );
   </script>
   

6. Create a page level validation
   • Type: PL/SQL - Function Returning Boolean
   • Sequence: 1
   • Validation Name: Recaptcha Check
   • Error Display Location: Inline in Notification
   • Validation:
     

     BEGIN
         :P7_RECAPTCHA_ERROR := checkRecaptcha
                                  ( pPrivateKey => 'your_private_key'
                                  , pChallenge  => :P7_RECAPTCHA_CHALLENGE_FIELD
                                  , pResponse   => :P7_RECAPTCHA_RESPONSE_FIELD
                                  );
         RETURN (:P7_RECAPTCHA_ERROR IS NULL);
     END;

   • Error Message: Recapture verification failed!

That's all, you are done!

So what was the challenge?

The JavaScript code provided by reCAPTURE creates two new fields on the fly. But because they do not comply with the names of the wwv_flow.accept package, which is called when a page is submitted, you will get an error during submit. So I had to use a trick. The nice side effect was that I can use the regular referencing in Oracle APEX, because they are just normal page items. See the comment text of the JavaScript code for details.

And here comes the stored function:

CREATE OR REPLACE FUNCTION checkRecaptcha
  ( pPrivateKey IN VARCHAR2
  , pRemoteIP   IN VARCHAR2 := OWA_Util.get_cgi_env('REMOTE_ADDR')
  , pChallenge  IN VARCHAR2
  , pResponse   IN VARCHAR2
  )
  RETURN VARCHAR2
IS
    --
    vRequest      Utl_Http.req;
    vPostText     VARCHAR2(500);
    vResponse     Utl_Http.resp;
    vResponseText VARCHAR2(2000);
    vError        VARCHAR2(200);
BEGIN
    ----------------------------------------------------------------------------
    -- Build text for the post action.
    -- For a field description, see
    -- http://recaptcha.net/apidocs/captcha/
    ----------------------------------------------------------------------------
    vPostText :=
      'privatekey='||Utl_Url.escape(pPrivateKey, TRUE)||CHR(38)||
      'remoteip='  ||Utl_Url.escape(pRemoteIP,   TRUE)||CHR(38)||
      'challenge=' ||Utl_Url.escape(pChallenge,  TRUE)||CHR(38)||
      'response='  ||Utl_Url.escape(pResponse,   TRUE)||CHR(38);
    ----------------------------------------------------------------------------
    -- if you need to set a proxy, uncomment next line.
    ----------------------------------------------------------------------------
    /* Utl_Http.set_proxy('proxy.it.my-company.com', 'my-company.com'); */
    ----------------------------------------------------------------------------
    -- Send data to reCAPTCHA.
    ----------------------------------------------------------------------------
    vRequest := Utl_Http.begin_request
                  ( url    => 'http://api-verify.recaptcha.net/verify'
                  , method => 'POST'
                  );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Type'
      , value => 'application/x-www-form-urlencoded'
      );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Length'
      , value => LENGTH(vPostText)
      );
    Utl_Http.write_text
      ( r    => vRequest
      , data => vPostText
      );
    vResponse := Utl_Http.get_response(vRequest);
    IF vResponse.status_code = '200' -- the HTTP call was successful
    THEN
        Utl_Http.read_text(vResponse, vResponseText);
        -- Has the user entered a correct solution?
        IF vResponseText LIKE 'false%'
        THEN
            vError := SUBSTR(vResponseText, 7);
        END IF;
    ELSE
        vError := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
    END IF;
    --
    Utl_Http.end_response(vResponse);
    --
    RETURN vError;
END checkRecaptcha;

« ... Read full posting ... »

Another blogger from the Oracle APEX team!

I want to welcome David Peake, the product manager for Oracle Application Express (APEX) in the Oracle APEX blogging community. A quote from David

My intention is to use this Blog to inform people on new things happening within APEX. One of my other reasons for starting the blog is because there are times when I need to get things from the Community as well.

And he immediately started to request things from the community :-). He wants to build a new section on the OTN Oracle APEX Page with a showcase of Internet applications built using APEX.

Check out his blog and give him a nice welcome!

David, looking forward to see you at OOW in SF!

« ... Read full posting ... »