Denes Kubicek ApEx BLOG

APEX 4.0 / 4.1 New Features

2011-12-19T21:51:00.006+01:00

Our next training is scheduled for March 2012. You can find the deatils on www.opal-consulting.de.

-------------------------------------------------------------------------------------

Wir (Denes Kubicek und Dietmar Aust) freuen uns, das nächste APEX Training bekannt geben zu können.

Es findet am 26.03.2012 bis zum 27.03.2012 erneut in Bensheim bei Frankfurt statt, im Aleehotel.

Nutzen Sie das Wissen und die Erfahrung von

* Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle Apex, aktiv in den OTN Foren zu Apex und Oracle XE, mit Präsentationen auf den letzten DOAG Veranstaltungen und
* Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und APEX mit multisite Applikationen, Preisträger des "Oracle APEX Developer of the Year 2008" Awards des Oracle Magazines, ein Oracle ACE Director und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation,

um die Nuntzung von APEX 4.0 und 4.1 New Features aus vielen erfolgreichen APEX Projekten zu lernen.

Neben einer Fülle an Informationen, die wir in vielen erfolgreichen APEX Projekten erarbeitet haben, sind insbesondere die abendlichen Sessions (von 19:00-21:00) als Highlight zu nennen.

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.

Weitere Details zum Kurs sowie die Anmeldung finden Sie auf www.opal-consulting.de.

Wir freuen uns, dieses Mal Peter Raganitsch als Gastredner bei uns im Kurs zu haben. Er ist in der APEX Community ebenfalls sehr bekannt und hält regelmäßig Vorträge zur Oracle APEX. Er wird das Thema "Mobile APEX: Anwendungen für Smartphones und Tablets entwickeln" behandeln.

Working with Tabs

2011-09-19T09:13:00.002+02:00

Creating two level tabs page layout in APEX is still a problem. I just had an application where I created a parent tab set and then a standard tab. Opeining the application would show the parent tab set and after choosing it the whole parent tab set would dissappear from the screen, showing only the newly created standard tab set. If this happens to you, you will need to do one more thing to get it working:

1. edit your standard tab,
2. go to "Parent Tab Set" section and
3. choose the parent tab set (probably you will have only one there)
4. save the changes

Invalid PL/SQL function in the branch conditional processing

2011-08-16T15:05:00.002+02:00

If you use PL/SQL functions in your branch conditional processing you should be sure your code works. I just had a case where I added something to my code and I didn't test it. It took me a while to discover that the code doesn't compile. The branch still worked ignoring the condition.

Expert Oracle APEX - New Book

2011-07-08T09:35:00.005+02:00

I almost forgot to blog about this. You may have seen this already - there is a new book out there written by several people verry well known in the APEX comunity. I was also asked if I would like to participate and decided to write on Tabular Forms. I think this is a great book and if you are interested in APEX then you should get one. All the funds will be donated to the families of two of our passed away colleagues:

- Carl Backstrom
- Scott Spadafore

The links to the book you can find at Amazon or at Apress.

Enjoy.

SSO with IBM TAM

2011-05-25T10:55:00.004+02:00

In my second project involving IBM Tivoli Access Manger SSO I experienced a problem with Interactive Report sorting and utilization of different jQuery techniques. There is a nice description on how to set up APEX to work with IBM TAM SSO available here:

http://www.estherhoppe.de/tom/tipps/ibm-tam/how-to-INTEGRATE-APEX-WITH-IBM-TAM.html

One more thing needs to be added to this document. Probably, your IBM Admin will set it up using junctions. As I said, this will cause problems with Ajax since IBM TAM will add a small javascript to the end of the HTTP response. Something like:

<script>
document.cookie = "IV_JCT=%2Fjunction_name";
</script>

and APEX will have problems with interpreting that response. Json, used for IR sorting will not work at all.

To work around this problem, you will need to talk to the administrators and let them set it up using mapping tables as described in this document:

http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/index.jsp?topic=/com.ibm.itame2.doc_5.1/am51_webseal_guide91.htm

Oracle APEX: Knowhow aus der Praxis - jetzt auf APEX 4.0!

2011-02-06T12:51:00.004+01:00

Because of the great response to our training offerings, we decided to schedule another one this year. You can find the deatils on www.opal-consulting.de.

-------------------------------------------------------------------------------------

Wir (Denes Kubicek und Dietmar Aust) freuen uns, das nächste APEX Training bekannt geben zu können.

Es findet am 30.05.2011 bis zum 01.06.2011 erneut in Bensheim bei Frankfurt statt, im Aleehotel.

Nutzen Sie das Wissen und die Erfahrung von

* Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle Apex, aktiv in den OTN Foren zu Apex und Oracle XE, mit Präsentationen auf den letzten DOAG Veranstaltungen und
* Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und APEX mit multisite Applikationen, diesjähriger Preisträger des "Oracle APEX Developer of the Year 2008" Awards des Oracle Magazines und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation,

um Best Practice Vorgehensweisen aus vielen erfolgreichen APEX Projekten zu lernen.

Neben einer Fülle an Informationen, die wir in vielen erfolgreichen APEX Projekten erarbeitet haben, sind insbesondere die abendlichen Sessions (von 19:00-21:00) als Highlight zu nennen.

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.

Weitere Details zum Kurs sowie die Anmeldung finden Sie auf www.opal-consulting.de.

APEX 4.0 New Features - Wiederholung

2010-11-10T20:31:00.004+01:00

Das Interesse an unserem letzten Kurs "APEX 4.0 New Features" war so gross, dass wir es vorzeitig schliessen mussten. Wir werden deswegen diesen Kurs am 24. und am 25. Januar 2011 wiederholen. Eine Neuigkeit dazu gibt es auch: wir werden im Rahmen des Kurses auch eine zusätzliche Hands-On Session haben, in der wir zeigen werden, wie man eigene Plug-Ins entwickelt. Sie können sich zum Kurs ab sofort auf unserer Hompage anmelden.

Bis bald.

Tabular Form - Highlighting and Focus in Validations

2010-10-18T11:55:00.003+02:00

I like the new feature of 4.0 tabular form validations a lot. If you create a validation on a column, you will get the highlighting and focus out of the box. Based on that, I created a small example for those cases where you can't use the standard validations and still need to do it by hand. It works the similar way except that the highlighting will only work after clicking the link in the message. You can find that example in my Demo Application.

SQL Query (return colon separated value)

2010-09-28T19:08:00.002+02:00

Did you know there is a new computation type in APEX - SQL Query (return colon separated value)? Probably not, because there are many other "sexier" features. However this "small" feature is realy cool. Before, you needed to do several steps if you wanted to compute your multiselect list, shuttle item, checkbox or a radio button:

1. DECLARE an array,
2. SELECT * BULK COLLECT INTO array
3. use APEX_UTIL package to convert that array into a string
4. RETURN that string

With APEX 4 you don't need to do that any more. You just do a normal select and whatever it returns will be concatenated into a colon delimited string. That's it.

New Apex Blog

2010-09-03T13:49:00.006+02:00

My colleague and friend Linh Dinh from Munich recently started to post about his experience with APEX. You can find his blog here http://www.dinh.de. Linh is a knowledgable and smart person with a lot of Java background. His first contact with APEX was in March this year when we started with a project for one of my customers. He immediately liked it and has made a huge progress since then. Linh is blogging in German and therefore his postings are especially interesting for the German APEX community.

Oracle Apex 4.0 Training: New Features

2010-07-29T17:51:00.004+02:00

Because of overlapping with the OOW agenda, we decided to reschedule our training to 4th and 5th of October 2010.

Wir (Dietmar und ich) haben den Termin für unsere Schulung um zwei Wochen verschoben. Die Schulung findet nun am 04.10 bzw. 05.10.2010. Der Grund war die Überschneidung mit dem OOW Termin. In diesem Training möchten wir folgende Punkte ansprechen:

- Überblick der neuen Features, was ist alles neu
- Der Umgang mit Websheets, wie kann man die neuen Wiki-ähnliche Funktionalitäten sinnvoll nutzen
- Dynamic Actions, wie man AJAX Funktionalität deklarativ (ohne zu programmieren) in seinen Applikationen einsetzt
- Erweiterbarkeit von APEX über Plug-Ins, benutzerdefinierten Elementtypen und Regionen
- Verbesserungen der Tabular Forms, insbesondere die Validierungen und die Unterstützung zusätzlicher Elementtypen
- Die Verwendung der neuen Elementtypen
- Verbesserungen der mächtigen interaktiven Berichte mit komplexeren Filtern, Gruppierungen und das Abonnement von Benachrichtigungen
- Team Development, das Projekt direkt mit APEX managen
- Verbesserungen im Application Builder
- Neue Möglichkeiten der Chart-Engine sowie neue Diagrammtypen (GANTT und Kartendarstellungen)
- Web Services, deklarative Unterstützung von REST-Webservices
- Administration, Vereinfachungen in der Administration sowie im Deployment von Applikationen
- Umgang mit dem neuen APEX Listener

Alle Details findet ihr hier:

http://www.opal-consulting.de

Highlight Selected Row

2010-07-23T10:08:00.006+02:00

This may be an a bit old topic. However I am getting emails from time to time asking how to do that. This example shows how to easily highlight a selected row in a report and make it persistant while reloading / submitting the page.

APEX 4.0 - Standard Tabular Forms and Select Lists

2010-07-11T15:44:00.008+02:00

I just noticed that the bug I first time saw in the first beta version of 4.0 is still present - see this forum posting and especially this one. If you have a wizard generated tabular form with a select list and you hit the F5 button to reload the page, your select list values will change. This happens only if you use FF. With IE instead it works fine. See this example:

http://apex.oracle.com/pls/otn/f?p=31517:11

If you are already using 4.0 in production, you should do something to avoid unexpected results. I am sure that the Apex-Team will correct this bug with the first patch for 4.0.

The actual workarround, as posted in the second thread dealing with this isssue is to switch the "Autocomplete" to "off" in the page attributes.

Save Changes before Exiting

2010-05-24T15:48:00.005+02:00

One of my customers wants to replace some Forms applications using APEX. One of the critical issues was the possibility to warn the user before exiting the existing form (switching tabs, closing browser window or tab or simply using the back button). In this example in my Demo Application I created a proof of concept using some older threads and demos. The solution is quite neat since there is almost no hardcoding and it works in every case while trying to move from the form. One of the important things was how to get that button for saving changes work, so it doesn't trigger the alert but does save the changes.

Tabular Form - Preserve Changes

2010-03-11T15:40:00.007+01:00

In our last training (08.03-10.03.2010) there was an interesting question on how to preserve the changes made in a tabular form while paginating through it. The requirement was also to save all the changes made independant of the visible set of rows. This problem can be solved using apex collections. This page in my Demo Application holds a working example including the full description of the code.

Preselect Select List Values while Adding Rows

2010-02-13T21:35:00.002+01:00

This example is showing how to preselect values in a tabular form select list while adding rows.

Preventing Duplicate Entries in a Tabular Form

2010-02-13T20:05:00.004+01:00

This may sound trivial but the question is one of the frequently asked question in the forum. I just created an example showing how to check a tabular form on duplicate entries prior to saving the new added rows. You will find the example here. Try adding some rows and enter an already existing name for the ename.

APEX Training in Bensheim - die Uhr tickt

2010-02-02T09:46:00.007+01:00

Liebe APEX-ler, auch dieses Jahr organisieren Dietmar und ich ein weiteres Training zum Thema APEX aus der Praxis. Der Termin ist 08.03 - 10.03 im Aleehotel Europa in Bensheim an der Bergstrasse. Wenn ihr auf der Suche nach einem APEX-Training seid, in dem nicht nach einem empno, deptno Muster unterrichtet wird und wo der Dozent APEX nicht nur aus den Unterlagen kennt, sondern in komplexen Projekten selbst eingesetzt hat, dann seid ihr bei uns richtig. Eine Anmeldung zum Training findet ihr hier, auf Opal-Consulting.de.

Same procedure as every year - APEX Stammtisch

2010-02-02T09:32:00.003+01:00

Dietmar aust und ich werden am 22.02.2010 um 19 Uhr unseren jährlichen APEX Stammtisch in Siegburg organisieren. Falls ihr Lust habt mit uns und anderen Teilnehmern über APEX zu reden so könnt ihr euch hier anmelden.

My (our) first book

2009-12-23T13:31:00.003+01:00

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

2009-11-08T17:30:00.004+01:00

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

2009-10-01T12:17:00.003+02:00

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

2009-09-02T12:24:00.007+02:00

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

2009-08-27T11:35:00.007+02:00

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

2009-08-21T12:54:00.001+02:00

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

2009-08-20T13:39:00.006+02:00

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

2009-08-18T18:51:00.003+02:00

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

2009-06-14T23:29:00.005+02:00

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

2009-06-14T19:14:00.010+02:00

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

2009-06-04T20:58:00.009+02:00

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

2009-06-04T10:32:00.007+02:00

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

2009-06-02T12:33:00.005+02:00

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

2009-06-01T10:10:00.004+02:00

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

2009-05-30T14:22:00.012+02:00

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

2009-05-30T09:50:00.006+02:00

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

2009-05-29T13:05:00.004+02:00

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

2009-05-28T10:24:00.009+02:00

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

2009-05-16T13:47:00.005+02:00

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

2009-03-19T13:33:00.009+01:00

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

2009-01-29T23:17:00.005+01:00

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)

2009-01-23T16:46:00.003+01:00

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

2009-01-17T17:29:00.006+01:00

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

2009-01-09T15:45:00.005+01:00

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.

Dynamic Charts

2008-10-19T10:05:00.005+02:00

Whenever you need to create a chart with multiple series, you will go there and hardcode those series, creating an almost identical SQL for each one of them. As long as your values remain the same, that will be ok. But what if they change? Actualy, the solution is pretty simple - you need to create a PL/SQL Block to return all the series in one SQL. This example in my Demo Application shows how to write such a Function.

If you replace the column and table names, you can use the same function for all of your charts with multiple series.

Enjoy.

Tabular Form - Checkbox for Setting values to 'Y' or 'N'

2008-10-05T22:39:00.005+02:00

This is also one of frequently asked question in the Apex Forum:

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

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

Using a checkbox for deleting rows is fine, but how do you use it if you want to flag the records and have a value updated depending on the checkbox status (valid/invalid, yes/no)?

It was Patrick Wolf who initially helped me to understand how a checkbox array works. He also created an example (on the fly) to show this can be managed. However, this example was based on a standard tabular form and it couldn't be reporoduced by many interested visitors of my Demo Application. Since this question has been repeated several times in the last couple of weeks, I decided to create a new example, which is based on a manual tabular form.

So, if you have a similar requirement, which is there in many cases, you may want to have a look here:

http://apex.oracle.com/pls/otn/f?p=31517:219

Validation with Ajax

2008-10-01T11:03:00.005+02:00

As soon as you need to do some date or number validations using javascript, you will notice how cumbersome that attempt could be. Just recently, I had to programm an application which needed some date and number validations prior to the page submittion. I found that using ajax and letting your database do those kind of cheks is a quite elegant way.

In my Demo Application you will find an example

http://apex.oracle.com/pls/otn/f?p=31517:217

showing how easy it is to set it up.

Especially the date validation is funny. It will accept all kinds of date formats:

sep/15.08 03:16:00 AM

sep.15-08 03:16:00 AM

sep/15/08 03

and it will, of course, give you a user friendly message if you go too far and try entering something like this:

14.15-08 03:16:00 AM

or for

10.15-08 13:16:00 AM

you will receive

Try changing your browser language settings from english to any other language and do the excercise again.

OOW

2008-09-19T17:00:00.007+02:00

It is time to get my luggage prepared for a long trip to San Francisco. This will be my first OOW. The trip will be long and exhausting but I am happy because I will get a chance to meet many of my colleagues, the most of which I know only through emails or the oracle forums.

I will arrive on Saturday afternoon and stay there till Thursday evening. These will be five exciting days and the programm is starting already on Sunday. I was invited by Dimitri Gielis to participate in the "Ask the Experts" session together with some well known names from the apex community: John Scott, David Peake, Scott Spendolini and Anton Nielsen.

Also, Dimitri announced an Apex Meetup on Tuesday evening.

The target is to catch as many apex sessions as possible. Unfortunatelly, some of them are starting at the same time. It will be hard to make a decission which one to take.

I will stay at the King George hotel, which is on a walk distance to the Moscone Center.

So, if you are also attending this great event and you are interested in apex, there is a good chance we will meet there.

Oracle Forums Splash

2008-09-18T17:04:00.012+02:00

I got this message at least five times today:

at http://www.oracle.com/splash/forums/index.html.

Having to read this for the fifth time, I was just asking myself what sense do these couple of sentences make.

"Oracle forums is experiencing technical difficulty."

So, what kind of a technical difficulty are you experiencing day by day since almost a month? Furthermore: how long is this going to last? Another month? Or two? Any ideas?

"We are aware of the issue and are working as quick as possible to correct the issue."

Hm, are you sure you are?

"Please try again in a few moments."

Well, I did but it still doesn't work.

"We apologize for any inconvenience this may have caused."

Nice to know.

"To speak with an Oracle sales representative: 1.800.ORACLE1."

What should I tell him?

"To contact Oracle Corporate Headquarters from anywhere in the world: 1.650.506.7000."

I don't think they would want to talk to me.

"To get technical support in the United States: 1.800.633.0738."

Unfortunatelly, I belong to the rest of the world.

Guys, if this continues, I will never ever collect enogh points to become a guru.

What's wrong with the Oracle Forum?

2008-08-28T14:12:00.004+02:00

As you probably noticed, the forums are either:

- not available at all or

- taking a lot of time to load or

- if they ever load, they are not working properly and showing errors if you try to create or update a posting.

If you manage to load those pages, you will notice that there are some new featueres implemented. Some of them are usefull. Like the new text editor (I realy like that one). Some of them are however questionable:

- the new reward system where you can get some points if you provide a helpfull or even a correct answer. The funniest thing is that the asker is supposed to say the answer someone provided is "correct".

- the fonts were changed to 11px which is not readable at all,

- the visited property they implemented are i.m.h.o. unneccessary because of the image showing if the posting was opened before or updated since. It is anoying too. Hard to read because of the grey color on a grey background.

I already asked the people who set it up if they are realy sure this makes sense. They didn't answer. Well, I am probably not the only one who asked.

Despite of the changes made, features I like or those I don't like, the most important thing though is the availability of the forum. And the fact is - it is not available since almost a week. Have they done a test before they upgraded? How did they test it? Did they ask the comunity about the changes? Maybe they should roll it back once again and consider a redisign using ApEx.

Oracle Text in Five Short Steps

2008-08-27T12:10:00.011+02:00

Almost every application I create contains a BLOB (file) table for storing files available for a download. In the most of the cases it will store some documentation related to the application - user manuals, howto's and similar stuff. The download functionality is a nice thing but how about searching the content of those files? There are some examples arround showing how to do that in ApEx but in my opinion the most of them are far to complex. I created an example in my Demo Application, showing how to do that in less than five minutes:

http://apex.oracle.com/pls/otn/f?p=31517:76

This example includes the most frequently used functionalities:

1. mixed case search

2. highlighted snippets

3. index synchronization on commit

If you need more, you can always look into the Oracle Text Documentation.

Enjoy.

Form Validation and User Friendly Messages

2008-08-26T23:10:00.004+02:00

I am getting this kind of questions quite often:

"I have report with form for modifications. After clicking on the link the form related to this report pops up. Often this is done by at least two users trying to modify one and the same record simultaneously. In such cases the application raises - "ORA-20001: Current version of data in database has changed since initiated update process. Error Unable to process row ....(table name)".

Could you give some advice or give and example how to handle this in a more user friendly manner?"

I created an example in my Demo Application showing how this can be done:

http://apex.oracle.com/pls/otn/f?p=31517:210

which also includes a step by step guideline.

There, you may test the validation by starting a concurrent update process and trying to update the data on the parent page.

Add Rows to a Standard Tabular Form using Item Value

2008-08-24T15:36:00.005+02:00

There are many threads regarding this issue in the forum. Many of them without any answer. So, how do you add rows to your standard tabular form based on an item value?
If you follow this example:

http://apex.oracle.com/pls/otn/f?p=31517:209

you will see that you need three small adjustments to the standard process:

1. extend your SQL using UNION_ALL by adding a small SQL selecting NULL's from DUAL followed by CONNECT BY LEVEL <= :P1_ADD_ROWS_ITEM,

2. add a conditional branch, first in the sequence, passing a request ADD to your page

3. do a modification to the standard add rows process to add 0 rows

and there you go. You don't need to write a manual process to get that functionality
applied on your standard tabular form.

Enjoy.

Dynamic Tabular Form

2008-08-07T13:48:00.006+02:00

It can happen that you get a requiremen similar to this one:

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

1. You have an unknow number of tables

2. You don't know the structure of the tables in advance

3. You still need forms for entering and maintaining data

The only solution that comes to my mind are dynamically created tabular forms. Tabular forms have already a bad reputation. Now, if you add the word "dynamical" to it, it will sound even more horrorful.

As I promised in the mentioned thread, I tried to create a working example. This is what I came up with:

http://apex.oracle.com/pls/otn/f?p=31517:195

It took me arround three hours to get that code together. Of course, this can be done much better - using a package and providing some additional functionalities like validations and other item types.

Multiple Cascading Select Lists (again)

2008-08-06T23:07:00.007+02:00

This is an issue for many developers. I see a lot of questions and receive a lot of mails on this topic. I thought that demonstrating how to do that with two items would be enough to guess how to do it with more than that, since the approach is the same. However, there is nothing better than a step by step guide to get it working.

You will find the extended example here:

http://apex.oracle.com/pls/otn/f?p=31517:119

My advice, from what I have seen so far debuging the other people examples, is to:

1. be carefull with copy-paste and before you do it, think of replacing the given item , table and procedure names with your own,

2. test your on-demand processes in the SQL workshop first, to see if they run,

3. avoid multiplying your code through copy-paste. This makes it much easier to debug and

4. if you have no experience with a similar solution, try to reduce your scope and do it on a simple example first. Try using emp, dept tables like I do it in my Demo Application.

Enjoy.

Default Sorting of a Report

2008-08-06T12:48:00.004+02:00

This was an interesting question:

"My requirement is Report should always display the original sequence (in the same way if I run the report in TOAD or SQL*PLUS, ...) and when I click the column heading then only it should do the actual sorting. Now, as soon as I set the sortable attribute to YES for a column and running the report for the first time... it's doing the sorting based on the that column."

So, how do you do that? Normaly, if you enable sorting on an apex report, it will save the sorting preference per user in an item and will call this preference next time you login.

I used the rownum to display the "original sequence" on initial load of the page and enabled the sorting on that column as well. Putting this column to be the first in the row on columns with enabled sorting and hiding it would do the trick. The second thing I did was to remove the sorting preference on the initial load and keep the rownum ordering as long as the user decides to sort the report.

You can view a working example here:

http://apex.oracle.com/pls/otn/f?p=31517:13

How to post your code in the Oracle Forum

2008-07-23T16:33:00.004+02:00

This may have been already posted somewhere else. However, I still see people not knowing how to post their code properly. If you want to show your code in a posting on

http://forums.oracle.com

use the following tags and put your code between them

[code]

[/code]

This will show your code formated and readable like this


SELECT CASE
          WHEN cnt > 20
             THEN '<font color="red">' || cnt || '</font>'
          WHEN cnt <= 20
             THEN '<font color="green">' || cnt || '</font>'
       END AS x
  FROM "MYTABLE"
 WHERE TO_CHAR ("MYTABLE"."DATE", 'YYYY-MM-DD') >= '2007-04-01'
   AND "MYTABLE"."SERVCODE" <> 'OBS'

instead of something like this

SELECT CASE WHEN COUNT > 20 THEN '' || cnt || '' WHEN COUNT <= 20 THEN '' || cnt || '' END AS X from "MYTABLE" where TO_CHAR ("MYTABLE"."DATE",'YYYY-MM-DD') >='2007-04-01' and "MYTABLE"."SERVCODE" <>'OBS'

ApEx - Shopping Cart

2008-07-23T14:09:00.003+02:00

There is a new example in my Demo Application showing how to create a simple shopping cart using Ajax and DHTML tipps from Carl Backstrom. This demo doesn't require a single submit and it shows how to utilize DHTML and Ajax to operate on collections. Since there is quite a bit of code and I am fairly busy at the moment, all the code is available only to those who have an admin login to my workspace.

You will find the example here:

http://apex.oracle.com/pls/otn/f?p=31517:159

Enjoy digging into it.

Over 500 named users of my Workspace

2008-07-22T11:33:00.007+02:00

I started with the registration somewhere in January/February of this year. Up to now, more than 500 people applied for a login.

Within these five to six moths I answered arround 1200 emails regarding account and also many other questions related to ApEx. The number of emails I am getting per day is quite constant and I still haven't noticed a trend of those emails decreasing.

Sorting with ROWNUM

2008-06-30T17:39:00.002+02:00

Creating a query and including the ROWNUM will work only for those reports, with disabled column sorting. Once you enable the column sorting, the ROWNUM will not show the right order. This is because ApEx does the sorting after determining the ROWNUM.

A simple trick can help you to overcome this issue. Just escape the ApEx internal #ROWNUM# like this:


SELECT '#ROWNUM#' SEQUENCE, empno, ename, sal
  FROM emp;

and it will give you the right ROWNUM regardless of your sorting.

You can see that working in this example in my Demo Application.

Pipelined Functions

2008-06-29T13:29:00.005+02:00

The question in the forum was:


I need to allow my users to type in a list of names and return the names that are not in a table.

1. User types [ Tom Joe Bob MIKE ] into a Text Area item named :P2_NAME_CHECK

2. A table named CUST contains two rows with TOM and Bob in the 
   CUST_NAME column.

3. A report returns two rows JOE and MIKE.

Usually, it is oposite. You want to show the list of users that match the criteria.

The solution for this problem is quite easy if you know how to use pipelined functions. This is a case where the pipelined functions shine. I created an example in my Demo Application showing how this can be done.

Basically, the pipelined function will create a row for all substrings returning no rows from the emp table, showing those substrings as rows.

Textfield Item - Submitting the Page

2008-06-29T13:10:00.005+02:00

Normaly, if there are page items of type "Text Field" they will not submit the page unless you change them to "Text Field (always submits page when Enter pressed)". If you have only one item on you page of type "Text Field" this behaviour changes and the page will always submit if you press "Enter". Sometimes, you don't want this to happen. Especially, if you have some javascript attached to your field. To avoid that you can use the following trick:

1. create another item on you page of type "Text Field",

2. in the "HTML Form Element Attributes" of that item put


style="display:none"

The result is that the second item will not be displayed and the first item will not submit on pressing "Enter".

Two Tabular Forms on one Page

2008-06-29T12:14:00.004+02:00

Recently there have been several discussions in the forum regarding multiple tabular forms on one page. I thought it is easy if you know how to create one tabular form manually, you will be able to do it for any other number of those forms. However, many participants of the forum do not share my opinion. This is why I decided to create a very basic example showing how that works. You will find it in my Demo Application together with the required code. If you want to see all the details, you may apply for an account following the instructions on the login page of my Demo Application.

Have fun.

Problems loading Oracle Forums in a browser?

2008-05-17T11:04:00.004+02:00

I had this with my old laptop and thought that is my "overloaded" FireFox with all those extensions. However, that continued with my new notebook as well. Now, I noticed this is not only FireFox but also the same thing happens when I use Internet Explorer (which I use only then when I need to open the same ApEx application twice).

See the picture below. It looks like while loading the forum the page is not rendered completely. This behaviour is only there when using Oracle Forums and they happen in both cases - while loading the main page or a single thread. Therefor my question to all readers of this blog: have you ever noticed something like that? I have this from time to time regardless of the connection I use. Currently, I use DSL 16000.

Limit or Extend a Datepicker in ApEx

2008-05-12T12:04:00.017+02:00

I have received a couple of questions per email, similar to this one on limiting the value of a date picker item. That shows this is an issue that needs to be solved from the development team. Whereby this is an easy issue. If you look behind the code, you will se there are one procedure and one view involved. The procedure code is generating a small pop-up window showing a calendar based on the number of the years the view is returning. I managed to get that customized by doing the following:

First of all, create a package like this:

1.


CREATE OR REPLACE PACKAGE limit_datepicker
AS
   x   NUMBER;
   y   NUMBER;

   FUNCTION get_x
      RETURN NUMBER;

   FUNCTION get_y
      RETURN NUMBER;

   PROCEDURE show_as_popup_calendar (
      p_request              IN   VARCHAR2 DEFAULT NULL,
      p_title                IN   VARCHAR2 DEFAULT NULL,
      p_yyyy                 IN   VARCHAR2 DEFAULT NULL,
      p_mm                   IN   VARCHAR2 DEFAULT NULL,
      p_dd                   IN   VARCHAR2 DEFAULT NULL,
      p_hh                   IN   VARCHAR2 DEFAULT NULL,
      p_mi                   IN   VARCHAR2 DEFAULT NULL,
      p_pm                   IN   VARCHAR2 DEFAULT NULL,
      p_element_index        IN   VARCHAR2 DEFAULT NULL,
      p_form_index           IN   VARCHAR2 DEFAULT NULL,
      p_date_format          IN   VARCHAR2 DEFAULT 'MM/DD/YYYY',
      p_bgcolor              IN   VARCHAR2 DEFAULT '#336699',
      p_white_foreground     IN   VARCHAR2 DEFAULT 'Y',
      p_application_format   IN   VARCHAR2 DEFAULT 'N',
      p_lang                 IN   VARCHAR2 DEFAULT NULL,
      p_application_id       IN   VARCHAR2 DEFAULT NULL,
      p_security_group_id    IN   VARCHAR2 DEFAULT NULL,
      p_start_year           IN   NUMBER DEFAULT 1919,
      p_end_year             IN   NUMBER DEFAULT 2050
   );
END limit_datepicker;
/

CREATE OR REPLACE PACKAGE BODY limit_datepicker
AS
   FUNCTION get_x
      RETURN NUMBER
   IS
   BEGIN
      RETURN limit_datepicker.x;
   END get_x;

   FUNCTION get_y
      RETURN NUMBER
   IS
   BEGIN
      RETURN limit_datepicker.y;
   END get_y;

   PROCEDURE show_as_popup_calendar (
      p_request              IN   VARCHAR2 DEFAULT NULL,
      p_title                IN   VARCHAR2 DEFAULT NULL,
      p_yyyy                 IN   VARCHAR2 DEFAULT NULL,
      p_mm                   IN   VARCHAR2 DEFAULT NULL,
      p_dd                   IN   VARCHAR2 DEFAULT NULL,
      p_hh                   IN   VARCHAR2 DEFAULT NULL,
      p_mi                   IN   VARCHAR2 DEFAULT NULL,
      p_pm                   IN   VARCHAR2 DEFAULT NULL,
      p_element_index        IN   VARCHAR2 DEFAULT NULL,
      p_form_index           IN   VARCHAR2 DEFAULT NULL,
      p_date_format          IN   VARCHAR2 DEFAULT 'MM/DD/YYYY',
      p_bgcolor              IN   VARCHAR2 DEFAULT '#336699',
      p_white_foreground     IN   VARCHAR2 DEFAULT 'Y',
      p_application_format   IN   VARCHAR2 DEFAULT 'N',
      p_lang                 IN   VARCHAR2 DEFAULT NULL,
      p_application_id       IN   VARCHAR2 DEFAULT NULL,
      p_security_group_id    IN   VARCHAR2 DEFAULT NULL,
      p_start_year           IN   NUMBER DEFAULT 1919,
      p_end_year             IN   NUMBER DEFAULT 2050
   )
   IS
   BEGIN
      limit_datepicker.x := p_start_year;
      limit_datepicker.y := p_end_year;
      wwv_flow_utilities.show_as_popup_calendar (p_request,
                                                 p_title,
                                                 p_yyyy,
                                                 p_mm,
                                                 p_dd,
                                                 p_hh,
                                                 p_mi,
                                                 p_pm,
                                                 p_element_index,
                                                 p_form_index,
                                                 p_date_format,
                                                 p_bgcolor,
                                                 p_white_foreground,
                                                 p_application_format,
                                                 p_lang,
                                                 p_application_id,
                                                 p_security_group_id
                                                );
   END;
END limit_datepicker;
/

2. GRANT EXECUTE ON limit_datepicker TO PUBLIC;

3. CREATE OR REPLACE PUBLIC SYNONYM limit_datepicker FOR <<your_schema>>.limit_datepicker;

To be able to limit the values the view returns you need to do the following:

1. log in a SYS

2. ALTER SESSION SET current_schema=FLOWS_030000 (yor flows schema, FLOWS_030000 in my case)

3.


CREATE OR REPLACE VIEW wwv_flow_years
AS
   SELECT     NVL (b.start_year - 1, 1918) + LEVEL
         FROM DUAL a,
              (SELECT limit_datepicker.get_x start_year
                 FROM DUAL) b,
              (SELECT limit_datepicker.get_y end_year
                 FROM DUAL) c
   CONNECT BY LEVEL < NVL ((c.end_year + 1) - (b.start_year - 1), 
                           2051 - 1918)

The view will now return the same result for the standard date picker or the value you request if you use the custom date picker.

And finaly, create the following on your page, which will contain the date picker item, limiting the years to the values you determine;

1. Create a hidden item on your page (Page 1 in my case)

P1_SECURITY_GROUP_ID

with a source PL/SQL Expression or Function

htmldb_custom_auth.get_security_group_id

2. Create the javascript and put it in the header of your page. Please note, you need to take care of the parameters yourself - for example p_yyyy or p_mm:


<script type="text/javascript">
function f_popup_date(p_this) 
{     
   var item_name = $x(p_this).name
   var app_id = &APP_ID.
   var sec_gr_id = $x('P1_SECURITY_GROUP_ID').value

       w = open("limit_datepicker.show_as_popup_calendar" +
                   "?p_element_index=" + escape(item_name) +
                   "&p_form_index=" + escape('0') +
                   "&p_date_format=" + escape('DD-MON-RR') +
                   "&p_bgcolor=" + escape('#666666') +
                   "&p_dd=" + escape('') +
                   "&p_hh=" + escape('') +
                   "&p_mi=" + escape('') +
                   "&p_pm=" +
                   "&p_yyyy=" + escape('2008') +
                   "&p_lang=" + escape('en') +
                   "&p_application_format=" + escape('N') +
                   "&p_application_id=" + escape(app_id) +
                   "&p_security_group_id=" + escape(sec_gr_id) +
                   "&p_start_year=" + escape('2007') +
                   "&p_end_year=" + escape('2008') +
                   "&p_mm=" + escape('01'),
                   "winLov",
                   "Scrollbars=no,resizable=yes,width=258,height=210");
       if (w.opener == null)
         w.opener = self;
       w.focus();
}
</script>

You will need to replace the values to what you need (2007 to 2008 in my case).

3. Date-Picker Item (P1_DATE_FROM in my case) is a normal text item with the following code in the Post Element Text:


<a href="javascript:f_popup_date('P1_DATE_FROM');">
<img src="/i/asfdcldr.gif" 
style="cursor:pointer;valign:bottom" /></a>

The downside of this approach is that you need to modify one of the apex views and take care you recreate it after the next update. I still didn't have time to find out why this doesn't work on XE. I will have a look into that issue soon and post the results here.

Cascading Select List in a Tabular Form

2008-04-30T19:17:00.008+02:00

This is something the most of the ApEx Developers have a problem with. How do I create a cascading select list in a tabular form? There are several examples (also in my Demo Application) on how to do that in a simple form. However, there is only one example arround showing how this can be done - in Vikas application, but I don't like that approach. I have also been asked several times to create such an example. Here we go! In my Demo Application you will find a working example showing also how to create an update and delete process + some javascript for checking and highlighting of all rows in your tabular form:

http://htmldb.oracle.com/pls/otn/f?p=31517:176

This approach is using apex_item + the same javascript (ajax) code as for the "simple" cascading select list.

Apply for an account if you would like to see all of the code behind this example.

XE Webfolder with Vista

2008-04-28T13:29:00.003+02:00

If you work with Vista and would like to create a Web Folder according to this tipp from Dietmar Aust, you will not be successful. In Vista there are some changes made and the path described in the above post will give you a view only.

In Vista you need to do the following:

1. Open Windows Explorer and go to Menu Tools / Map Network Drive

2. There, you click on "Connect to a Web site that you can use to store your documents and pictures"

3. Click two times on "Next" (don't ask me why!)

4. Enter http://127.0.0.1:8080/i where it asks you for an Internet or Network address,

5. Enter system / password

6. Give your connection a name

7. Finish.

Now, the images directory will appear in your Windows Explorer under Webfolder:

ApEx Training - München 02.06.2008 - 04.06.2008

2008-04-06T17:13:00.007+02:00

Liebe ApEx-ler,

Unser nächstes Training - Fortgeschrittene Techniken aus der Praxis - wird in München im Arabella Sheraton Westpark Hotel stattfinden. Die Details für die Anmeldung findet Ihr auf Opal Consulting.

Wir versprechen (und halten):

- Interessante und neue Inhalte
- hohen Praxisbezug
- Kompetenz in Oracle und ApEx Themen
- Antworten auf alle Ihre Fragen

und freuen uns Euch dort begrüssen zu dürfen.

----------------------------------------------------------------------------------------------------------------------
Our next Training will be held in Munich from 02.06.2008 to 04.06.2008 in Arabella Sheraton Westpark Hotel. The details you can find on Opal Consulting. Please note that places are limited and there has been a great deal of interest in the training following the previous event in March 2008. We advise you to register a.s.a.p.

ApEx Training - followup

2008-04-01T22:07:00.005+02:00

This was my first ApEx-only training. I can't remember when I last time spoke in front of that many people (at school perhaps). However, this was an amazing experience. ApEx is really something special - amazing people, exciting topics and a lot of enthusiasm. Also, there were 8 female attendees at our training which I think is great quote. Here some pictures from our training (actually, we were so busy that we managed to take only these two ;)):

The program schedule was quite well loaded and we didn't manage to elaborate on everything. Our schedule was also quite aggressive - training from 9 - 17.30 and QA Sessions from 19 to 21 (planned time - indeed it was 23 when we quit). Amazing to see 80% of the attendees sitting there at 7 in the evening preparing for the QA.

Tomorrow, we will post the details of our next training in Munich.

Manual Tabular Form

2008-04-01T21:11:00.003+02:00

Today, I answered an interesting question in the ApEx Forum. Basically, the requirement was to have a tabular form which allows only new records to be edited. Tabular forms are a powerful feature but also something the most of the ApEx developers have a problem with. The manual from the How-To's is outdated, confusing and it is also incorrect in several issues. Also, there are not many other resources in the community dedicated to the problem of operating with tabular forms. This is why I decided to create an example showing several important techniques for mastering this topic. I also posted the most of the code you need to get it working that way:

- highlighting selected rows
- validating the checksum
- disabling columns not allowed for editing
- adding rows to tabular form (one or multiple)
- operating with apex_item package

See the example in my Demo Application. If you want to see more, you may send me an email and join the group of 250 registered users of my Workspace.

A similar example was one of the topics in our training two weeks ago and was created by Patrick Wolf and myself.

ApEx Training - Update

2008-03-06T14:37:00.009+01:00

This posting is targeting the German ApEx comunity and is therefor also posted in German language:

Hallo liebe ApEx-ler. Unser Kurs im März http://www.opal-consulting.de/training ist leider seit etwa zwei Wochen ausgebucht. Wir planen einen neuen für Anfang Juni in München, da die Anfrage unerwartet gross ausfiel. Gleich nach dem Abschlus vom Training im März, werden wir den neuen Termin auf unserer Seite publizieren. Falls Sie bis dahin nicht warten wollen, können Sie uns auch gerne eine E-Mail an training@opal-consulting.de zuschicken und einen Platz vorreservieren.

Hello ApEx community. Our Workshop in March http://www.opal-consulting.de/training has been fully booked since almost two weeks. We are planing another one beginning of June in Munich. We will post the details right after the workshop in March.

ApEx Flashchart Headache

2008-02-22T20:55:00.008+01:00

Something like this can drive you nuts. I lost several hours trying to figure out why something that is supposed to work (and I do see it working), doesn't work for my customer. I created a simple drill-down chart and did my usual testing. After completing the work, I forwarded it to my customer to see if everything is as they expected. However, they complained it didn't show the right values. It took me a while to remember I answered one similar question in the forum here by proposing a workarround. I do my work in FF and my customer uses IE. There we go.

The problem seem to be simple - IE doesn't refresh a flash chart once the link has been used for the second time within a session. I was curious and checked if this is also the case with XML Charts. XML Charts did behave the same way if I used IE. It was easy to solve the problem with XML Chart package by adding an additional parameter to the link in form of a random number

v_random := DBMS_RANDOM.random;

After adding this, the problem was not there any more. I think, the same needs to be done with the Flash Charts in ApEx in one of the next releases. Eventually using the same method.

If you want to see a practical example of what I'm talking about, go to my Demo Application

http://apex.oracle.com/pls/otn/f?p=31517:169

and follow these steps:

1. Use IE (Internet Explorer),

2. Make sure your Internet Options / Browsing History / Settings is either set to "automatically" or "never",

3. Make a round trip and click all three pie slices of the pie chart showing departments,

4. After you click on any of the slices for the second time, the second flash chart will not show you the right result but will remain the same all the time.

5. You will need to submit the page in order to get the values change.

And now, the best thing is that the XML Chart will always show the right values. Even if the IE settings Internet Options / Browsing History / Settings are set to "never".

ApEx Item - Workarround

2008-02-13T14:11:00.008+01:00

There may be some cases where you need to use apex_item (although, Patrick Wolf doesn't think so). The downside of that approach is:

1. your Query looks awkward,

2. you can't sort on your columns,

3. you can't display your column totals

In case you didn't know, you may use a trick to work around that problem:

a) crate a normal query like this:


SELECT LPAD (ROWNUM, 4, 0) ROW_NUM, deptno, empno, ename, 
       sal, comm
  FROM emp

b) In the HTML expression of your columns put the following for each column accordingly:


<input id="f03_#ROW_NUM#" type="text" style="text-align: right;" 
value="#EMPNO#" maxlength="4" size="4" name="f03"/>

c) You may use the items in your custom update process and in your javascript the same way you do when you use apex_item or the wizard generated table.

Now, you may create column totals and sort on such created items. And your SQL query looks much better.

As always, there is a proof of concept in my Demo Application on this page http://htmldb.oracle.com/pls/otn/f?p=31517:168.

By the way. Around 100 people from all over the globe asked and received a personal account for the Demo Application. If you are also interested to look behind the curtains, please feel free to send me an email with your contact details.

Sorting on apex_item

2008-02-08T13:40:00.000+01:00

If you use apex_item syntax in your query, you will not be able to sort on the columns in a way you would expect because ApEx will sort the column on the string you use as input. This means it will sort on something like

<input id="f04_9" type="text" style="text-align: right;" value="2300" maxlength="12" size="12" name="f04"/>

To overcome this problem you may want to use the following in front of your apex_item:

'<INPUT TYPE="HIDDEN" VALUE="'
|| LPAD (sal, 20, '0')
|| '" />'
|| apex_item.text (4,
TO_CHAR (sal),
12,
12,
'style="text-align:right" ',
'f04_' || ROWNUM,
NULL
) sal_editable

You could use any other value instead of the original one as well (ROWNUM, ename) and use it for sorting.

Here, you will find a working example:

http://htmldb.oracle.com/pls/otn/f?p=31517:167

Enjoy.

Filtering a Shuttle Item

2008-02-05T15:58:00.000+01:00

Very often a shuttle item is much convenient than a multiselect list. But what if your LOV has many hundreds or even thousands of items? Check this example:

http://htmldb.oracle.com/pls/otn/f?p=31517:166

The most of the code can be reused except of the application process. However, this
could be written as a dynamic PL/SQL block as well.

The example I used is based on a table containing around 5000 records. Having in mind this runs on apex.oracle.com, the performance of filtering is quite satisfying.

Report with Filtering

2008-01-31T11:39:00.000+01:00

As far as I know, something similar should be a part of the ApEx version 3.1. Nevertheless, I wanted to know if I can do something like that myself. The requirement is:

1. a report containing a search field for each column (textfield, select list or other)

2. after typing in the select criteria, the report should filter the results,

3. no page submit required.

Here is the result of what I came up with. It is quite easy (and dirty). There, you will also find the full code I used, except of the DHTML Region Pull. That one, you can find in the Carl's Demo Application.

The downsides of this example:

a) the code is not generic - you got to do that yourself ;)

b) the sorting on the column headers doesn't work in that particular example - I avoided that since it would mean a much more work.

Maybe, you will find it usefull and apply it on one or the other case.

ApEx Training

2008-01-22T19:26:00.000+01:00

This posting is targeting the German ApEx comunity and is therefor posted in German language:

Wie Sie möglicherweise schon gehört haben, organisieren Patrick, Dietmar und ich einen ApEx Training im März dieses Jahres. Dieses Training heisst "Fortgeschrittene Techniken aus der Praxis". Das bedeutet natürlich nicht, dass diejenigen, die sich anmelden wollen, fortgeschrittene Kentnisse mitbringen müssen. Vielmehr ist unser Ziel, eine Schulung zu organisieren, die stärker praxisorientiert ist als wir das ansonsten kennen. Wir werden viele wichtige Themen ansprechen - vom Anwendungskonzept, über Layout bis zu den ganz modernen Themen wie z.B. Ajax. Interesse? Ueber dieses Link geht es zu den Infos und zur Anmeldung

http://www.opal-consulting.de

Wir freuen uns Sie begruessen zu duerfen!

Tabular form - updating a value in another column Vol. 2

2008-01-22T12:37:00.001+01:00

If you need to deal with a tabular form created by the wizard, you will need to use a slightly different approach. I discussed that topic yesterday evening with Patrick Wolf. The difference to the first approach you can find here:

ApEx Demo Appication - Javascript Set Display Item II.

The full explanation and the code are there as well. Basically, what you need to change is the way you get the rownum. Using $x(this).id will give you the current item id. Substr(4) will give you the last four digits of it (thanks Patrick) and putting that four digits in the Number() function gives you the row number (ROWNUM). You could use that value to loop through the table and update other values, not corresponding to that particular row.

Access to my Workspace

2008-01-22T12:13:00.000+01:00

My workspace dkubicek and my ApEx Demo Application on apex.oracle.com still show around 3000 page clicks per day in average. Google Analytics says there are up to 300 visitors with an average time on site of 11 minutes. 30% of the visitors are visiting the site for the first time.

Yesterday, I changed the login for the guest user in my workspace. In the past I was allowing access to my workspace with the same login as well. However, this showed to be a bad practice. Once, the application was deleted by someone and I needed my backup to restore it. Now, I require your email and your full name to give you your own access. But, I still need to ask you not to do any changes and especially not to install other applications in my workspace.

If you want an access to the workspace where the ApEx Demo Application is stored, please send me your email with your contact details and I will respond to you with a username and a password.

Tabular form - updating a value in another column Vol. 1

2008-01-21T13:35:00.000+01:00

Just recently, I had a requirement from one of my customers to create a kind of a form similar to excel, where user could update particular columns and this update will be applied (calculated) to the other columns, before the form is submitted.

First, I tried the solution posted in the forum, where you need to loop through a table using html_CascadeUpTill and and then find the row position. In some constelations this didn't work (vertical report template). I decided to look for alternate solutions and found out that this can be done in a more transparent way by simply using apex_item package and the ROWNUM. Also, the javascript code you need to do the update is much more transparent and much easier to debug. I also created a validation process to make sure only numbers are entered and an update statement for saving the changes to the corresponding table.

If you want to see a working example, go to my ApEx Demo Appication. There, you will find the full code.

ApEx Stammtisch - ApEx Meeting in Siegburg

2008-01-10T13:21:00.000+01:00

Dietmar and myself would like to establish regular meetings for the ApEx community in Germany. Therefor, we will start with our first meeting ("Stammtisch" in German) in Siegburg, on January 15, 2008. The target is to exchange the experience with other colleagues from the ApEx community. Further details you can find here:

http://www.opal-consulting.de

You are welcome to join us,

Denes

-----------------------------------------------------------------------------------------------------------------

Es war Dietmars Idee, einen sog. ApEx Stammtisch zu etablieren! Er hat sie am Ende auch umgesetzt (Danke Dietmar!). Wir starten am 15.01.2008 in Siegburg. Der Plan ist diese Runde in regelmässigen Abständen zu organisieren, um die Erfahrungen aus der ApEx-Welt auszutauschen. Die Anmeldung zum Stammtisch findet ihr hier:

http://www.opal-consulting.de

Wir freuen uns auf ein Kennenlernen und bis zum nächsten Dienstag.

Denes

Create Virtual Tables Vol.2

2008-01-07T18:24:00.000+01:00

Every once in a while a question similar to this one will appear in the forum. The problem is always the same: "how to create a lot of data based on very few records?". Today, one new variation of a puzzle followed. It took me arround half an hour in order to create a demo page, giving an idea on how to solve the problem. Of course, you need much more for a working version of an application.

The example in my demo application

http://htmldb.oracle.com/pls/otn/f?p=31517:158

is related to one of the examples from the past

http://htmldb.oracle.com/pls/otn/f?p=31517:83

which I already blogged about here

http://deneskubicek.blogspot.com/2007/05/create-pseudo-tables.html

Still, this requirement is somewhat new and different. It is worth of looking into it.

Export to Excel - 1500 downloads

2008-01-07T13:54:00.000+01:00

The package I created for downloading ApEx reports into Excel (instead of .csv) has now been available for about ten months. Some time after releasing the package I started to count the downloads. There have been more than 1500 downloads so far. There are two versions of the package - one for ApEx and one for XE. The ApEx Package has been downloaded around 1200 times and the XE version arround 300 times. I have to admit I hardly touched the package since the first release. However, it could be improved a lot to cover some functionalities .csv offers. I already talked to Patrick Wolf to put some common efforts into the improvement of the package in the near future. I would also like to thank to Bernhard Kahles and Martin Wibbing for their contributions to the improvments in the past, which helped to overcome some version related limitations.

Here is the link to the package once again.

http://htmldb.oracle.com/pls/otn/f?p=31517:108

Tabular Form on a table without a primary key (multiple primary keys)

2007-11-05T16:01:00.000+01:00

One of the questions that frequently appear in the ApEx forum is "How to create a tabular form on a table without a primary key or on a table with multiple primary keys (more than two primary keys allowed by the wizard)?". The solution is simple and interesting - by using a view and two instead of triggers. The view is using the rowid column as a primary key. The two triggers - instead of update and instead of insert triggers - are managing the updates and inserts. See the example plus the corresponding code in my demo application here.

Interesting translation

2007-09-26T19:10:00.000+02:00

I haven't blogged for a while. Too busy with my own stuff - projects, customers and my own business. Today, I was testing some things and stumbled upon a funny translation. I created a package body and dropped it. Here is what I received back from SQL Plus in German:

Basically, what it says after dropping is:

"Package Body created"

And here is what it says in English:

But still, I like PL/SQL a lot. No doubts about that.

Displaying and Sorting

2007-07-12T11:48:00.000+02:00

Quite often there is a misunderstanding between displaying of a value and sorting upon the same. ApEx reports are giving you a possibility to show one value and sort upon a different value, which you even may not want to show to the user.

As always, I created a small example in my demo application containing a full explanation and a link to the corresponding thread in the forum:

http://htmldb.oracle.com/pls/otn/f?p=31517:153

It is easy to set it up and it shows the power of ApEx once again - it's simplicity.

Multiple Reports in one Region

2007-06-13T00:18:00.000+02:00

One of frequently asked questions in the forum is "how to put more than one region in
a region?". For example a report and a list or two or more reports. There is a simple way - hardcoded - for solving this problem.

In my Demo Application there is an example showing this:

What I did was the following:

1. I copied a report region template and modified it slightly,

2. Created a copy of my standard page template,

3. Included the report template in it, just before the #BOX_BODY#,

4. In the #BODY# section of the report template, I put the #REGION_POSITION_05# and #REGION_POSITION_06# instead,

5. Now, using the page template and putting the reports in the #REGION_POSITION_05# and #REGION_POSITION_06#, was giving me the result I needed.

European ApEx Training

2007-06-03T10:30:00.000+02:00

I have been asked many times about a possibility to get some good training for ApEx. I think, there is an oracle two-day training on basics. However, what people want is usually more than that. Some weeks ago, the European APEX Training Days of the APEX Evangelists was announced. This will be a 3-day training covering advanced ApEx issues like ajax, security, customizing templates and many, many others.

If you are interested, you can benefit from the knowledge of John and Dimitri.

John is Oracle APEX developer of the year 2006 and is the author of the book "Pro Oracle Application Express" - a person with an Avatar level in ApEx.

Dimitri is a well known contributor on the OTN Oracle APEX forum and speaker at Collab'07. He is a creator of the popular dgturnament application and has many years of experience with Oracle.

ApEx - Replacing Binds Procedure

2007-05-18T00:11:00.000+02:00

If you need to use the apex_collection.crate_collection_from_query package, you will need to replace your :P_ITEM binds with v('P_ITEM') in your SQL, in order to get it working. Sometimes, it could be a pain. Using the following procedure will do the work for you:


CREATE OR REPLACE PROCEDURE replace_binds (
   p_sql_in    IN       VARCHAR2,
   p_sql_out   OUT      VARCHAR2
)
IS
   v_sql      VARCHAR2 (32767);
   v_names    DBMS_SQL.varchar2_table;
   v_pos      NUMBER;
   v_length   NUMBER;
   v_exit     NUMBER;
BEGIN
   v_sql := p_sql_in;
   v_names := wwv_flow_utilities.get_binds (v_sql);

   FOR i IN 1 .. v_names.COUNT
   LOOP

      <<do_it_again>>
      v_pos := INSTR (LOWER (v_sql), LOWER (v_names (i)));
      v_length := LENGTH (LOWER (v_names (i)));
      v_sql :=
            SUBSTR (v_sql, 1, v_pos - 1)
         || v_names (i)
         || SUBSTR (v_sql, v_pos + v_length);
      v_sql :=
         REPLACE (v_sql,
                  UPPER (v_names (i)),
                  'v(''' || LTRIM (v_names (i), ':') || ''')'
                 );

      IF INSTR (LOWER (v_sql), LOWER (v_names (i))) > 0
      THEN
         GOTO do_it_again;
      END IF;
   END LOOP;

   p_sql_out := v_sql;
END replace_binds;

And you no longer need to worry about modifying your code.

Create Virtual Tables

2007-05-15T22:23:00.000+02:00

This is one of frequently asked questions:

"I have only a few lines of data - mostly some dates with a duration from > to - and would like to use this as table to select from and populate a calendar. This calendar is supposed to show one entry for each day within a time period. How do I do that? Do I need to create a separate table?"

The answer is "No, you don't.". Using CONNECT BY LEVEL, you can create virtual tables and use those in your select statements. For example: there was a question in this post, how to show all Fridays within a certain period of time. Depending on your NLS settings you would do something like this to create a virtual table, showing all Fridays from the beginning of the year to the current day:


SELECT each_day "friday"
  FROM (SELECT       (  TRUNC (SYSDATE)
                      - TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd'))
                     )
                   + LEVEL each_day
              FROM DUAL
        CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd')))
 WHERE TO_CHAR (each_day, 'D') = '5'

Depending on your NLS settings, a Friday could be the fifth or the sixth day of the week.

In my demo application, you will find an interesting example on how to populate a calender for three events (three records) stored in a table, getting for each day within a given period of time, one entry in a calendar:

http://htmldb.oracle.com/pls/otn/f?p=31517:83

Almost too trivial for a post

2007-05-15T08:36:00.000+02:00

I have been using SQL for years and from time to time I stumble upon a problem, where I needed to count the occurrence of a character or a string within another string. Searching for a standard function in Oracle gives me no results. Browsing in the good old SQL forum gave me an idea on how to create my own function:


CREATE OR REPLACE FUNCTION string_occurrence (
   p_string      IN   VARCHAR2,
   p_substring   IN   VARCHAR2
)
   RETURN NUMBER
IS
   v_occurrence   NUMBER;
BEGIN
   v_occurrence :=
        (  LENGTH (p_string)
         - NVL (LENGTH (REPLACE (p_string, 
                                 p_substring, '')), 0)
        )
      / LENGTH (p_substring);
   RETURN v_occurrence;
END string_occurrence;

The simplicity of the idea is fascinating:

1. since you can't count it directly, you count the total string,

2. after that you replace the string you search for with '' (nothing) and count again,

3. you subtract the second value from the first and

4. you divide the result by the length of the string you want to count the occurrence for.

And there we go.

Ajax - Query by Example

2007-05-14T11:53:00.000+02:00

This is one of the frequently asked questions in the ApEx forum. I created an example on how to build a form which will work similar to the Forms QBE here.

The solution could be more generic. I have the idea to store the result of a query in a collection and access that collection instead of repeating the query for each click on the "Previous" and "Next" button. However, I encountered a problem creating a collection from an On Demand process. I posted this problem here and I'm waiting for an answer. If there is one, I could get rid of some lines of code in the original proposal.

Export to Excel - Unwrapped

2007-05-03T10:28:00.000+02:00

The packages for Export to Excel for ApEx and XE are now unwrapped and available for download:

http://htmldb.oracle.com/pls/otn/f?p=31517:108

http://htmldb.oracle.com/pls/otn/f?p=31517:109

You may use them and modify as you need. It has been downloaded arround 300 times in the last two months. Shows that it works and it is needed, despite of the new BI publisher feature available for ApEx 3.0.

The method is quite simple. You need to generate HTML tables and it will be readable as a normal .xls file from 97 to the latest version. This gives an option to create multiple pages in excel and export multiple reports in one run as one file. Of course, it needs some modification within the package to do that.

Have fun.

ApEx + Javascript = Ajax

2007-04-26T10:34:00.000+02:00

Understanding the syntax of javascript is a hard piece of work. I have to admit, that I just started to understand some small portions of it. However, many of the functions you need, have already been written by someone and they are available for a free download. A simple Google-Search will bring you there. But the real power of javascript in ApEx comes as a combination of it and PL/SQL, called Ajax. In my demo application, thera are several examples on how to utilize javascript and a page process on demand. It saves time and looks much better than refreshing a complete page. Here, you can find examples for:

- selecting a value using select list,
- populating multiple items using select list,
- cascading select and pop-up lists,
- creating a shopping cart,
- calculating date differences and
- using the famous Auto-Complete functionality.

Many of the examples are comming from Carl Backstrom. I just modified them a little bit. All the pages include a detailed description on how to set it up + the corresponding code.

Have fun!

Custom Search Form

2007-04-10T14:26:00.000+02:00

Many questions in the ApEx Forum are related to the way of creating custom search forms and implementation of different page elements in query filtering. I created an example a while ago and had to point to it several times since. I think, this is worth of posting once again. You will find the mentioned example here. It shows how to implement a filtering functionality using four different page elements:

- text field
- drop-down list
- date picker and
- a checkbox

Checkbox Nightmare

2007-04-05T09:31:00.000+02:00

There are only very few things in ApEx you could describe as critical. One of those, for sure, are checkboxes in tabular forms. I had a requirement to use those items in a updatable tabular form for setting simple flags like "Y" or "N", by clicking a checkbox. You will agree, this isn't something special if you think of oder programs operating on databases - evan Access has this functionality.

I spent several hours trying to solve that issue and finaly I had to give up. I asked Patrick Wolf, our expert for tabular forms for help. He was so kind to look into the application and correct it. And it worked. You can see the example here.

My request to the ApEx team:

Please solve this issue in one of the next releases. I can't really sell this problem to my customers. It is too trivial to be taken serious. Thanks.

ApEx is Oracle

2007-03-26T09:45:00.000+02:00

Very often I get asked if ApEx can do this or that, what usually is a functionality available in Oracle. Recently, there was a question if using ApEx, you can operate with nested tables (varrays). My answer is: Sure you can! However, it always depends what you want do do with it.

I created a simple example here. It shows how simple it is to create, insert and query upon nested tables using ApEx.

Export to XML format

2007-03-16T13:31:00.000+01:00

I received couple of emails asking me how to export a report to an .xml file. Therefore I decided to create a small package and a demo application showing how to do that.

You can have a look here:

http://htmldb.oracle.com/pls/otn/f?p=31517:118

and read the corresponding thread here:

http://forums.oracle.com/forums/thread.jspa?messageID=1740225

Basically, you need to fetch the region source and replace bind variables with the v('ITEM') function first. Second, you use the DBMS_XMLQUERY.getxml package to transfer you SQL Query into a result set, formated as XML. After that, you just need to cut your result into smaller portions and create an .xml file out of it.

This will not work for lower versions than 2.2 or XE. For those, you need to replace the part of the package fetching the region source with this block:

SELECT plug_source
INTO v_sql
FROM wwv_flow_page_plugs
WHERE ID = LTRIM (p_region, 'R')
AND page_id = p_page_id
AND flow_id = p_app_id;

and have the appropriate grants granted by sys on

GRANT SELECT ON flows_020100.wwv_flow_page_plugs TO <your_schema>

and

CREATE OR REPLACE PUBLIC SYNONYM wwv_flow_page_plugs FOR
flows_020100.wwv_flow_page_plugs

where flows_020100 is your html_db schema.

.csv Export Problem

2007-03-16T09:18:00.000+01:00

This problem is quite old. Many times my users complained getting no output when they try to export what they see in a report. I just found out that nobody was so really aware of this problem. Have a look at my example and the corresponding thread in the Oracle Forum.

4000 Page Clicks

2007-03-15T11:00:00.000+01:00

I never thought so many people from all arround the world will visit my Apex Demo Application. Yesterday, I saw more than 4000 pageclicks. Google Analytics says that over 250 visitors have opened my demo application within the last 24 hours. One of the biggest groups is located in Thousand Oaks and Redwood Shore in California - arround 10% of my visitors.

That's really cool.

ApEx Evangelists

2007-03-14T21:20:00.000+01:00

Couple of days ago, I was asked by Dimitri and John if I would like to join their project called ApEx Evangelists. This was an offer you can't refuse. You will find an entry about myself here.

I liked the idea of getting together and promoting ApEx - it is just cool. Also, I like the idea of having all these good people on board - Patric, Dietmar, Dimitri and John. Joining our efforts, we can offer an unbeatable quality of service, training and solutions.

So, ApEx Evangelists are coming to town - stay tuned.