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

Almost too trivial for a post

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

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

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

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

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

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

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

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

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

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

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.

Export to Excel

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 harddrive
2. 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.

In this

thread

I posted a solution for that problem. This package is still in work and once I'm done, I will post the full code behind.

You can see a demo and download the package including all installation instructions on my

Demo Application

This is a real one-step export and it works for both ApEx and XE. Surelly, this package can be enhanced in the future - now it exports your result set one-to-one into excel.

All my postings related to ApEx will point to this demo application on apex.oracle.com.

Tell us Denes, why do you want to BLOG?

So, let's start blogging. It took me a half a year to make that decission and to start doing it. I am going to focus my blogs on ApEx demos and solutions for different problems. Very often, in

ApEx forum

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.

Denes Kubicek