My (our) first book

As Dietmar Aust already wrote in his blog, our first book was published yesterday. We are currently the No. 1 under Oracle books on amazon.de. Although, this is changing every hour. The book is currently available only in German. Our goal was to write a book about the best practices we have developed through the years.



Merry Christmas to all of you.

Denes Kubicek

APEX Kurs März 2010

Dietmar und ich werden einen weiteren Kurs im März 2010 organisieren. In Kürze werden wir die Informationen darüber auf unserer Seite veröffentlichen.

Update Multiple Values in a Tabular Form using PopUp

In this posting and the corresponding example I showed how to compute columns in a tabular form row using a select list. Sometimes you can have a requirement to use more than one parameter (configurator) to do a preselection. In that case you would need to use a popup window. I was asked how that could be done and I created a slightly modified example in my demo application. The only difference is that:

- you need to tell the popup page the row number you would like to compute
- you need to call the ajax function out of the popup window using window.opener function

The Power of the Apex Repository

We all know about the apex views but hardly use them. Today, I answered two questions in the oracle Forum where the solution was related to the apex views. The first question was on how to create a report with all the csv links for all the reports in an application:

http://forums.oracle.com/forums/thread.jspa?threadID=952151

The second question was on how to provide a generic solution for a page help functionality:

http://forums.oracle.com/forums/thread.jspa?threadID=952469

Basically, the usage of apex views provides a simple two-step solution which doesn't require any maintanance.

In my demo application, you will find the corresponding examples:

Report on all csv links

Help page

Try it out.

Renaming Page Items

I am using Firefox for development of Apex applications. I have my builder open in one tab and the application in the next one. As soon as I make a change I would switch to the application tab and see the results after reloading or submiting the page. That is quite practical and saves a lot of clicks. Couple of days ago I was developing an application and had to rename a page item. This item was involved in some page computations and after renaming it the page didn't work the way I expected. I spent probably an hour trying to understand why. I changed my code couple of times withouth success. Then it came to my mind that I had seen this before. I logged out, logged in again and my code suddenly worked. So, this is just a small tip to first logout after renaming page items. It could save you a lot of time (and nerves) if you remember it.

Validations using Ajax

I posted about this in October 2008 but in different context. This example in my Demo Application shows how to use Ajax for item validation. I use this method in some cases where I can't allow the page to be submited but have to inform the user before he hits the submit button. Also, if user enters something invalid, it will overwrite that with a previous valid value.

Become a better Apex Developer

We are just under 1 month away from the next training, kicking off 21st of September in Aleehotel Europa in Bensheim. It is time for the "late" birds to sign up for the training.

Unser Training beginnt am 21.09.2009 in Bensheim - Allehotel Europa. Es ist Zeit sich anzumelden. Es sind diesmal einige Plätze frei im Vergleich zu den letzten Trainings. Möglicherweise sind das die Auswirkungen dieser Krise, von der ständig erzählt wird. Und wenn es tatsächlich eine geben sollte, dann ist das die Zeit, die man nutzen sollte nach vorne zu schauen und sich für die guten Zeiten vorzubereiten. Also, meldet euch...

Search and Edit Form with Report

It is easy to create a form with a report in order to drill-down and modify or create records. It would be great if Apex would have a wizard for creating a form which would give us a possibility not only to edit and create records but also to search on those. In this small example in my Demo Application you can see how to do that manually. This example shows several things:

- how to create a manual form
- how to create a manual fetch process
- how to ensure data integrity using checksum
- how to create a form for editing and searching data
- how to drill down on a record set returned by a search without loosing it



Enjoy.

Use Lightbox 2 in Apex

Following this thread I was wondering how easy it is to incorporate the Lightbox 2 plugin into Apex on apex.oracle.com. You can see the results here in my Demo Application.

Tabular Forms - Autofill

Tabular Forms are used to manage master-detail dependencies. Usually for creating applications like:

- Order Management or
- Quote Management

For order or a quote management you would create a simple form to manage your header data. Tabular form would be used for order lines having hidden fileds for primary key and foreign key (order primary key). Order lines are usually items comming from an item master table. Sometimes, these line items are available through a select list and the most of the information you need in a tabular form is already available and could be filled automatically:

- item description
- price
- quantity (default 1)
- other item properties

In this example in my Demo Application you can see how that can be done.



Selecting a product from the list would use Ajax to get the related information from the item master table for the selected product_id.

An on demand process would generate an xml stream and send it back to the page.

<body>
<desc>this xml genericly sets multiple items</desc>
<item id="f32_2">JavaScript: The Definitive Guide</item>
<item id="f33_2">31.49</item>
<item id="f34_2">David Flanagan</item>
<item id="f35_2">1</item>
</body>

A piece of javascript code would loop through that xml string and assign the values returned to the specified items in the tabular form.

How to Debug Ajax and On Demand Processes

I am getting several emails every week regarding one the same issue - Ajax and On Demand Process. In the most of the cases there is a problem with the On Demand Process code. Unfortunatelly Apex is not going to compile your code once you save it. If there is an error in your code the results will not be as expected. On the other side it is quite easy to debug that:

1. Try running your code in SQL Workshop - if your process is supposed to generate an XML file for a select list, you should see a select list if the code is O.K.



2. Use Firefox and download Firebug. Using Firebug you will be able to immediatelly see the results of the On Demand Process you created. Just open the Console and run your process:



3. Be carefull with naming of your On Demand Process - the name of your process in the javascript has to exactly match the name of the process in application processes. Using Firefox and Firebug will help to find that out:



4. Javascript is using htmldb_Get function to call your process the same way you would do that by typing it in the browser address bar - having a blank character there would retreive no results. That means that thhe part of the javascript code

htmldb_Get(null,html_GetElement('pFlowId').value,
'APPLICATION_PROCESS=CASCADING_SELECT_LIST1',0);
get.add('CASCADING_SELECTLIST_ITEM_1',pThis.value);

equals to

f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=CASCADING_SELECT_LIST1:
NO::CASCADING_SELECTLIST_ITEM_1:30

5. This would also mean that you can test your process using a simple link in your browser:



6. Make sure to name your application and page items correctly in the javascript part of the code. If your misstype a name no explicit error message will be issued. However, using Firefox and Firebug you will see a lot of javascript code in the Console and eventually find an error like displayed in the following screenshot:



7. Finaly, opening the "Post" Tab in Firebug, you will get an overview of your main parameters:



Enjoy debugging your ajax code.

Magic Page 0

In this posting I explained how to overwrite css rules using page header text or a conditional region. Something similar is also possible using the page 0. One of my customers asked me to create a page template in a corporate layout which they could use for their future applications. The only difference between the applications would be the image they display in the page header. The customers also had a mix of different browsers starting with IE6 over IE7 and Firefox. There were two problems I was faced with:

1. how to easily apply / change the image displayed in the header of the application

2. different browsers (IE6) would interpret my css code differently than the other browsers

I solved the problem in the following way:

1. created a page 0

2. created a "No Template" Region on the page

3. added the following code to the region source

<style type="text/css">
.top_header{background-image:
url(#IMAGE_PREFIX#themes/theme_basf/Sorp2.gif);
background-repeat: no-repeat;
background-position: 290px -45px;
background-color:#BFD0F4;}

.basf01overlapping{background-image:
url(#IMAGE_PREFIX#themes/theme_basf/Sorp2.gif);
background-repeat: no-repeat;
background-position: 290px -15px;}

</style>


<!--[if gte IE 6]>
<style type="text/css">
#navigationArea{width:99%;}
</style>
<![endif]-->

The style part of the code would display the custom image. The javascript part would overwrite the style for the navigationArea if IE6 is used to view the page. Once they create a new application the only thing that needs to be done is to copy the page 0 from one of the existing applications and change the image name.

Function Returning Links

In Apex you can create links out of your columns in a report. What you currently can't do is to put a condition behind this link and generate it upon that condition - if there is a link then it is there for all the rows. The only workarround for this is to create the link in your SQL. In that case your SQL may look quite clumsy and hard to read. Something like this:

SELECT CASE
          WHEN deptno = 10
             THEN    '<a href="javascript:popUp2('''
                  || 'f?p=&APP_ID.:500:&SESSION.::&DEBUG.::'
                  || 'P500_DEPTNO:'
                  || deptno
                  || ''', 700, 700);">'
                  || '<img src="#IMAGE_PREFIX#gobut.gif">'
                  || '</a>'
          WHEN deptno = 20
             THEN    '<a href="javascript:popUp2('''
                  || 'f?p=&APP_ID.:501:&SESSION.::&DEBUG.::'
                  || 'P501_DEPTNO:'
                  || deptno
                  || ''', 700, 700);">'
                  || '<img src="#IMAGE_PREFIX#go_search.gif">'
                  || '</a>'
          ELSE    '<a href="javascript:popUp2('''
               || 'f?p=&APP_ID.:502:&SESSION.::&DEBUG.::'
               || 'P502_DEPTNO:'
               || deptno
               || ''', 700, 700);">'
               || '<img src='
               || '"#WORKSPACE_IMAGES#go_button.gif">'
               || '</a>'
       END LINK,
       ename, job, hiredate
  FROM emp

The solution for this problem you can find here. Back in 2006 I created a function for generating links. This function can do the following for you:

- create a link based on a target page you provide, withouth having to code a full apex link,
- replace the link with an image if desired,
- sort the links based on any columns in your source,
- open the target page in the same window or in a popup window,
- add a request to your link,
- reset pagination if required,
- add a checksum to your link.

At the end, your query looks much better if you use that function:

SELECT CASE
          WHEN deptno = 10
             THEN return_link_fn (p_page         => 500,
                                  p_item         => 'P500_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'gobut.gif'
                                 )
          WHEN deptno = 20
             THEN return_link_fn (p_page         => 501,
                                  p_item         => 'P501_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'go_search.gif'
                                 )
             ELSE return_link_fn (p_page         => 502,
                                  p_item         => 'P502_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'go_button.gif'
                                 )
       END LINK,
       ename, job, hiredate
  FROM emp

Applying CSS Conditionally

It may happen that you have an exception on one or more of your pages where your styling deviates from standard. In addition to that, you may want to apply your style conditionally. You have several choices in order to solve that problem:

1. make changes to your css file
2. create a css file within your workspace and reference it in your page template
3. create an additional template for your new style

However, this reqiures severals steps and could be a problem in some cases:

- you are not managing your HTTP Server and your dba's need months in order to accept such a small change

- you need to make changes to your page templates

- you need to create an additional template (region template, report template, ...)

Sometimes you need to go that way but there are also situations where you could solve that as in this example in my Demo Application. Using Firefox and Web Developer Toolbar you can easily find out the classes and their properties referenced in your template. You can adjust those properties to whatever you need and put that code into the header text of your page:

<style>
.t10ReportsRegion .t10RegionBody {background-color:#E2E5EA;}
.t10ReportsRegion .t10ButtonHolder {background-color:#E2E5EA;}
</style>

and the style will be applied only on that particular page. This will overwrite the style applied through the class with the same name in your css file.

If you need to apply your style conditionally, you can do the following:

1. create a region with the highest sequence and do not apply any template to it,

2. put your code in the region source of that region

3. change the region to render conditionally

Checking an Authorization Scheme within a PL/SQL Block

Recently, I had a quite tricky requirement where within a customer application I needed to check if user has a required role to run a process, edit a field or see an information. Many times these conditions were a combination of different authorization schemes and other rules determined during the runtime of the application. So the problem was not solved by just adding an authorization scheme to the object. More than one authorization scheme needed to be checked together with some other conditions. Using the apex_util package and the function public_check_authorization I was able to successfully acomplish those tasks. This is a modified example of the code I used there:

BEGIN
   IF     (   apex_util.public_check_authorization ('AUTH_GROUP_1')
           OR apex_util.public_check_authorization ('AUTH_GROUP_2')
          )
      AND :p12_status = '0'
   THEN
      RETURN TRUE;
   ELSIF     (   apex_util.public_check_authorization ('AUTH_GROUP_2')
              OR apex_util.public_check_authorization ('AUTH_GROUP_3')
             )
         AND :p12_status = '1'
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

Execute Javascript through PL/SQL

Sometimes, like in this case, where I needed to set the focus on load to a specific line in a table when inserting new rows, you need to execute javascript within your PL/SQL block. In this example in my Demo Application you will find an explanation of the code you need for that.

Switch between Apex Versions

This is a part of the apex documentation but the fact is the most of us simply don't know about it. I have several customers and those customers have different versions of Apex installed. I am using XE on my Laptop to develop their applications. Therefore I need to be able to use different versions of apex simultaneously. With a help of the two simple scripts wrapped into an executable, I can switch between the versions in seconds:

ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000;
exec flows_030000.wwv_flow_upgrade.switch_schemas('APEX_030200','FLOWS_030000');

to switch to the version 3.0.1 and

ALTER SESSION SET CURRENT_SCHEMA = APEX_030200;
exec apex_030200.wwv_flow_upgrade.switch_schemas('FLOWS_030000','APEX_030200');

to go back to the latest release 3.2.

One Million Pageviews per Year

There are 1226 registered users of my workspace on apex.oracle.com. Arround 300 of those are still active. Many of those inactive accounts are using a download of My Demo Applicatiion installed localy.



If I run Google Analytics for the last 12 months I will get a nice analysis of the visits which says the following:

1. 1,001,992 Pageviews

2. 104,510 Visits

3. 9.6 Pages per Visit

4. 8.25 Minutes - Average Time on Site

These statistics do not include the clicks and visits done within the workspace while investigating the code behind the single pages. At peak times the application reaches 6000 pageviews a day.

New Training - September 2009

I was too busy to post about our sucessfull training in March this year. It is nice to see how the apex community is growing. It looks like every company using oracle is sooner or later going to consider using apex to build their applications.

We decided to schedule our next training for September of this year. More details you can find here.

Ich hatte kaum Zeit in den letzten Monaten. Auch nicht, um über unser letztes Training im März zu berichten. Die Anzahl der Teilnehmer übertraf unsere Erwartungen. Die Anzahl derer, die es aus terminlichen Gründen nicht schaffen konnten war ebenfalls sehr gross. Wir haben deswegen entschieden einen weiteren Termin im September zu organisieren. Wenn Sie Interesse haben, können Sie hier weitere Details über dieses Training finden.

Using Oracle Text with Table Data

Using LIKE clause with large tables may be a serious performance problem, involving full table scans for even a very small result set. The time you need to get a result will increase tremendously. Using Oracle Text may help you overcome those issues. In this simple example in my Demo Application I am showing how to start using Oracle Text on a table. There are also many other features included with Oracle Text such as preferred terms, fuzzy searches, highlighting, and much more. All explained in Oracle Text Documentation. Try it out.

Apex Stammtisch

Dietmar hat es hier gepostet:

APEX Stammtisch am 10.2.2009 in Siegburg

Wir organisieren am 10 Februar 2009 um 19:00 einen Stammtisch in Siegburg

Sion im Carre
Neue Poststr. 15
53721 Siegburg
http://www.s-im-carre.de/

Sie können sich hier anmelden.

Custom Export to CSV (again)

If you ever had to deal with exporting data different from the data you display, you probably visited this blog posting:

http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html

which shows how that can be done. The purpose of the post is a bit different - it shows how to adjust the format of the exported file. Just recently I had a requirement to display some reports created using analytic functions with some groupings and counts. At the same time, there was a requirement to export the data without the results of the analytics. I could have used Scott's posting but I was too lazy. Instead of that I used the following (simple) approach. Basically, you would create two reports - on in which you display your data and one (never displayed) for exporting. The only thing you need to do then is to modify the link label.

Setting Item Session State using Ajax

This question is a frequently asked one. So, how can you set a value of an item without submitting a page? Today, I answered that question in the Oracle Forum. In addition to that, I created an example in my Demo Application showing those few steps you need to make in order to achieve that. Basically, you just need a small piece of javascript in your page header and an "onchange" call of that javascript in the HTML Form Element Attributes of your item. The code is generic and you can just copy-paste it without a need to change anything.



Enjoy.

Apex Training

In diesen "Krisenzeiten" erzählen alle darüber wie alles schlecht ist und was alles noch schlechter wird... Das ist ziemlich lässtig und es macht schlechte Laune. Aber, was ist denn eine Krise? In meinen Augen, das ist auch eine grosse Chance etwas zu ändern. Natürlich, zum Besseren. Und es gibt so viele Möglichkeiten. Zum Beispiel eigene Apex-Kenntnisse zu verbessern. Patrick, Dietmar und ich organisieren im März ein weiteres Training in Bensheim im Alee-Hotel Europa, um Best Practice Vorgehensweisen aus vielen erfolgreichen APEX Projekten zu lernen.

Neben der Informationen, die wir uns in zahlreichen APEX Projekten angeeignet haben, kommen die abendlichen Sessions (von 19:00-??:00) hinzu.

Dort stehen wir Ihnen für zusätzliche Hands-On Sessions zur Verfügung, um die Beispiele des Tages durchzuarbeiten oder auch, um konkrete Fragestellungen in Ihren aktuellen Projekten zu diskutieren.



Profitieren Sie von unserem jahrelangen und praxiserprobtem Oracle APEX Knowhow! Die Agenda und weitere Details findet Ihr auf der Web-Site wo Ihr Euch auch anmelden könnt.