Monday, 5 November 2007

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.

Wednesday, 26 September 2007

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:

Thursday, 12 July 2007

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:

Wednesday, 13 June 2007

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

Sunday, 3 June 2007

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.

Friday, 18 May 2007

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:

Tuesday, 15 May 2007

"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:

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:

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:

Monday, 14 May 2007

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.

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.

Thursday, 26 April 2007

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:

Tuesday, 10 April 2007

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:

Thursday, 5 April 2007

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.

Monday, 26 March 2007

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.

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:

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.

Thursday, 15 March 2007

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.

Wednesday, 14 March 2007

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.

One of the most anoying things my users complain about (and all the time) is export of reports to excel. Let us not talk about how much sense does it make to do such exporting and what do they need it for. The fact is: it is required. The problem isn't so much that you need two steps instead of one:

1. export your report and save it as a .csv to your harddrive2. open it using excel

It is more a problem of different office versions and local settings (formats). Then you may need three or four steps instead of "only" two. Data formating may also become an issue.

there are interesting questions and solutions. Once, you post your solution it dissapears and maybe it could be usefull for the others. Creating this blog, I want to select the most interesting ideas and publish them once again on a separate place.