ApEx Training - Update

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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