How to reformat the highwatermark of a table after a delete?

For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.

Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.

I thought that this could have something to do with my indexes but I didn’t know how to fix this.

That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.

So how do you fix this?

If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.

First option, and the one that worked best in my case was: use TRUNCATE TABLE

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe :-)

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!

ODTUG Kaleidoscope 2008

Thursday night I got this fantastic news:

“Dear Karen:
Congratulations! Your abstract,
Use the Power of Apex Dictionary Views to Increase the Quality of Your Apex Applications,
has been accepted for presentation at ODTUG Kaleidoscope 2008, June 15-19, in New Orleans, Louisiana.”

Wauw!! A range of emotions ran through me. I go to New Orleans, the city from the gospel and the jazz!

It’s the first time I go to a foreign country to give a presentation, so you understand that I’m very excited to be one of the selected speakers at ODTUG! This is a wonderful opportunity!

The Apex-core team at iAdvise has developed their own QA-tool on top of the Apex Repository to guard the quality of our application guidelines and conventions. I will bring this case together with my colleague Jan Huyzentruyt. We already did a lot of presentations together in Belgium, but it is the first time we are doing it abroad …

So, we are looking forward going to New Orleans and we are honoured being on the list of speakers together with big names like Tom Kyte, Steven Feuerstein, Mark Rittman, Carl Backstrom, Patrick Wolf, …

Hope to see you there!

OWB Paris : What happened to “Generate intermediate results”???

Prior to OWB Paris, you could easily trap the SQL code that was being generated by OWB in a mapping : you simply had to rightclick on an outgoing group and select “Generate Intermediate Result”.

At first sight, this option disappeared in OWB 10G release 2, but this is not the case… Now, how can you get an intermediate result in OWB Paris? It’s simple … If you know it.

1. Select Generate from the top bar.

2. The Generation Results window will appear.

3. From the dropdown list, select “Intermediate” instead of “Full”.

4. Finally, select the outgoing group of which you want to see the intermediate result.

Like I said : simple, if you know it … ;o)

Using AJAX within Oracle Application Express (part 2)

Most of the AJAX calls will require some parameters. Instead of using an application item for every parameter, I wrote some javascript and PL/SQL functions that will store all AJAX parameters as XML in 1 application item!

The same javascript as in my first blog, but some extra parameters and functions are added.

This javascript function allows you to add an extra parameter to the AJAX call

This PL/SQL function allows you to read out the same parameter in the AJAX call’s PL/SQL code

We can simply add some parameters to our AJAX call. The first parameter of the AddParam function, is the name of the parameter (you must use the same name in your AJAX call’s PL/SQL code to read the parameter’s value), the second parameter is the parameter’s value.

In the PL/SQL code of the AJAX call, you can use the GetParameter function to read out these parameters

The result would be…

Using AJAX within Oracle Application Express

Everybody is already convinced about the strength and benefits of AJAX. There is only one thing that keeps bothering me when I’m using AJAX within Apex. For every AJAX call, you have to define a new application process.

I worked out a generic solution where you only need to create one application process for all your AJAX calls!
How to implement this generic solution
Step 1: Within Oracle Application Express
a) Create an application process and give it the name AJAX_DYNAMIC_PROCESS

b) Create an application item with the name AJAX_PROCEDURE

Instead of writing your PL/SQL code for the AJAX call in the application process, you have to write it on the database as a procedure (or a package procedure) Ex: pck$ajax_examples.prc$first_example

Step 2: Javascipt

Now we can put (by using javascript) the name of this procedure in the application item AJAX_PROCEDURE. The execute-immediate will execute the pck$ajax_examples.prc$first_example procedure.

I have written some javascript functions that simplify the use of AJAX:

Now implementing an AJAX call becomes as easy as this…

The ajaxResult variable will contain the result of your AJAX call!

This method is only recommended in an inTRAnet environment because your database is open to SQL Injections. You can also build in extra security, for example by checking your AJAX call againt a database table to make sure the call is permitted.

In a next blog I will explain how you also can generalise the passing of params to the Ajax call.

Solution for page refresh problems in ADF when no relationships are used.

I have a small program which is used for registering money transfers.

It has two tables, table A holds all the transfer information and table B keeps the total amount of money earned so far. There is no connection between the tables, there are only triggers on table A which keep table B up to date.

On the overview page it looks like this:

The problem that I had was that when I pressed “Create” and inserted a new record, the total amount field was not updated after the create page navigated back to the overview page. It didn’t refresh, but the trigger was called and executed. So, for example, after “2 very large bags of potatoes” was inserted the “Total Amount” field would still display 10, although if you peeked in table B it would say 30.

I don’t know if it’s the right solution, but I solved it this way:

In the page definition of the overview page I made a new action, an execute action based on the iterator of table B.

<action IterBinding=”TotalAmountIterator” id=”ExecuteTotalAmount”
DataControl=”SampleServiceDataControl” RequiresUpdateModel=”true”

To invoke it every time the page is loaded, you do this:

<invokeAction Binds=”ExecuteTotalAmount”

So far the Total Amount field will always be refreshed when the application navigates to the overview page.

There is only a small exception, the delete button. The effect of pressing the delete button – besides the deletion of the record – is a partial page refresh, so the main table is OK, but the total amount field is not.

I fixed it by calling the method I declared in the page definition in the deleteButton method in the backing bean of the overview page.

public String deleteButton_action() {
BindingContainer bindings = getBindings();
OperationBinding operationBinding =
Object result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“Commit”);
result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“ExecuteTotalAmount”);
result = operationBinding.execute();

return null; }

Oracle Forms: the road ahead…

At iAdvise we have about 12 full-time Oracle Forms consultants/developers, the complete Cronos group has got about 30 consultants/developers working with this ancient toolset ;-)

To stay up-to-date with current developments, I attended the “Oracle Forms: the road to SOA” seminar a few weeks ago (mar 6, 2007). This seminar was hosted by Oracle UK (London) and I think I was the only person from Belgium on site. Keynote speaker was Grant Ronald, Principal Product Manager in Oracle’s Tools division. He did a very good job explaining Oracle Forms is still alive and kicking . Even though a lot of people seem to doubt (some even regret) it, Oracle Forms will be around for many, many years to come.

A few months ago I also attended an Oracle web-seminar, hosted by Oracle HQ Forms Product Management (Jan Carlin) about new features in Forms 11. To be honest, I was one of the non-believers at first, just look at my comments on this post on the EuroTransplant blog.

Since then, I’ve read al lot of things about this issue and I’ve changed my opinion. Just look at some of these facts:
- Even though Grant could not provide exact numbers, he claimed 35-40% of all companies using an Oracle Database are also using Oracle Forms (custom build applications or Oracle Apps). Because of this very large install base, A LOT of money has been (and is being) invested in Oracle Forms. It’s naive to think any technology will take over this position in the short future.

- According to Grant, the Oracle Forms forum is in the Top 5 on OTN (only after SQL – PL/SQL – Database if I’m correct), with hundreds of new threads/messages each month. So, even after 20 years, Oracle Forms still is one of the most popular development platforms Oracle provides.

- Oracle’s current motto about Oracle Forms is “Upgrade & Integrate”. There are a lot of Oracle Forms applications worldwide which still run in character or Client-Server mode. In general it is much cheaper / quicker to upgrade these forms, instead of completely rebuilding them in a new environment. In fact, integration features of Oracle Forms 11 will make it even easier to leverage investments of the past.

- It’s been said many times before, but just read the Oracle statement of direction about Oracle Forms: Oracle Forms is supported until 2013 at least!

Quote from Jan Carlin (Oracle Forms product manager) on Jan 5,2007: Oracle have no plans to discontinue development or maintenance of Forms. Oracle Applications will continue to use Forms for at least another 10 years and we have many tens of thousands of customers using Forms for applications with thousands of users all over the world. We would be undermining ourselves if we desupported Forms any time soon.

With this in mind, I asked myself a few questions:
- What are claims like “Oracle webforms doesn’t have a real future anymore” or “Oracle Forms is history” based on? Which facts or official statements can you provide to support these statements?
- Why would you recommend a switch in development platforms to a customer if their current Forms investment perfectly fits their business needs?

I found no satisfying answer to any of these questions, so it’s pretty obvious I had to change my original opinion. I hope sales people will do the same ;-)

Feel free to give your opinion on this…


OWB 10g R2 : Help! Where are my windows?

Imagine … You’re enthousiastically editing a table in the Object Editor of OWB Paris. You minimize the Object Details window, and drag it to somewhere on the screen … Unfortunately, by a “slip of the mouse”, you drag it outside the OWB master window, for instance into your Windows task bar … Uups! The Object Details are … GONE! Although, according to the menu, the Object Details should still be visible !?

Un-checking and re-checking the Object Details in the menu doesn’t help; closing and re-opening the Object Editor doesn’t solve the problem either; even restarting OWB does not bring back the Object Details.

However, there is a way to “fix” this : in the folder [OWB_HOME]\owb\bin\admin, hence, there are a couple of XML-files, one of which is : SchemaEditorLayout.xml.
You should replace this file with an old copy of the same file that you got from another client install who hasn’t its Object Details vanished …

Closing en re-opening the editor should bring back the Object Details.

Tip : since I experienced this phenomenom, I made a backup of the *.xml files mentioned above … just in case, as this issue does not only apply the the Object Editor …


Get every new post delivered to your Inbox.

Join 30 other followers