Monday, 5 November 2007
Wednesday, 26 September 2007
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.
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:
It is easy to set it up and it shows the power of ApEx once again - it's simplicity.
Wednesday, 13 June 2007
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.
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.
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.
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:
CREATE OR REPLACE PROCEDURE replace_binds (
p_sql_in IN VARCHAR2,
p_sql_out OUT VARCHAR2
v_sql VARCHAR2 (32767);
v_sql := p_sql_in;
v_names := wwv_flow_utilities.get_binds (v_sql);
FOR i IN 1 .. v_names.COUNT
v_pos := INSTR (LOWER (v_sql), LOWER (v_names (i)));
v_length := LENGTH (LOWER (v_names (i)));
SUBSTR (v_sql, 1, v_pos - 1)
|| v_names (i)
|| SUBSTR (v_sql, v_pos + v_length);
UPPER (v_names (i)),
'v(''' || LTRIM (v_names (i), ':') || ''')'
IF INSTR (LOWER (v_sql), LOWER (v_names (i))) > 0
p_sql_out := v_sql;
And you no longer need to worry about modifying your code.
Tuesday, 15 May 2007
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
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:
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
( LENGTH (p_string)
- NVL (LENGTH (REPLACE (p_string,
p_substring, '')), 0)
/ LENGTH (p_substring);
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.
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.
Thursday, 3 May 2007
The packages for Export to Excel for ApEx and XE are now unwrapped and available for download:
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
- 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.
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:
- text field
- drop-down list
- date picker and
- a checkbox
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.
Friday, 16 March 2007
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:
and read the corresponding thread here:
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:
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>
CREATE OR REPLACE PUBLIC SYNONYM wwv_flow_page_plugs FOR
where flows_020100 is your html_db schema.
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.
That's really cool.
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.
So, ApEx Evangelists are coming to town - stay tuned.
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.
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
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.
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
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.