Oracle field wise in-line data check

1. Introduction

It took a bit of time, but I needed some time to get back into Oracle and the datawarehouse stuff. What I tried to implement was a possibility, to check data validity during datawarehouse-load in a generic and easy way and in particular field wise.

2. Datawarehouse Setup

What I often found in datawarehouse-projects is an at least two-tier implementation where the "raw" data is put into an import-table via SQLLoader. I know, that in reality the N in n-tier could be quite a lot, but let's assume, we just have a 2-tier database-load. These import-table just consist of VARCHAR2, so there is no validation done during import.

In a second step these data is transformed (e.g. the numbers are transformed into NUMBER fields) into a second table, the target table. Let's assume, the fields in the target table are named equally to the import table.

3. Finding Source and Target Fields

So first of all I want to determine all source fields that should be stored into the corresponding target field. I use the meta table all_tab_columns to achieve that.

4. Creating transformation statements

Now I use the information to create a select statament for the source table and an insert statement for the target table.

The v_insert string holds all the matching fields seperated by a comma. The v_bind holds all variables (the a colon combine with the field_name, e.g. :DATE_FIELD).

These variables are used, to build the queries:

5. Dynamic execution

In this step I wanted to execute them dynamically.

What we got, is a simple way for processing each column seperately. Now we can get a result for each columns transformation. Imagine the line where I bound the value to the insert statement. We could exchange that with a datatype-dependant field processing:

The above example shows the transformation for NUMBER and DATE fields. If an error occurs, it is logged field wise in the v_error variable.

6. Conclusion

This is a easy way to process an ETL with a field-wise type cast. You get an error for every field that doesn't match and not just one error per dataset.

New releases of app-engine plugin and gorm-jpa

1. gorm-jpa Release 0.7

While continuing my work with grails and Google's app-engine (GAE), I found a new version of the gorm-jpa plug-in.

The release now handles the Null-Pointer-Exception from my older blog. You can just skip the "Null-Pointer-Irritations".

Thanks to Graeme Rocher!!

2. app-engine Release 0.8.8

I also found a new release for the app-engine-plugin. The log says the following:

0.8.7 - Now possible to use standard run-app command and fixed Log4j errors

The change to version 0.8.8 is not fully clear. But it seems, that log4j is evicted in this release and reimplemented directly for this plugin. Maybe another bug fixed?

I tried the run-app command, but experience an recurring error:

What I found, was to uninstall the tomcat-plugin. I ran:

That was is! Simply uninstall tomcat plugin and the run-app command uses the jetty-engine to run your web-application.

3. Links

app-engine Plugin
gorm-jpa Plugin
Tomcat uninstall

app-engine & grails performance: a slight shine on the horizon

What I found on twitter: There hope! According to this tweet of Graeme Rocher the Google app-engine team did some performance profiling with grails applications. He intends to implement some performance enhancement in the version to come: 1.3

See yourself:

Graeme Rocher at Twitter

app-engine performance issues

1. Motivation

As I refered to in my last blog, I experience HardDeadlineExceededError from time to time. Particularly, when I hit my web application for the first time after a time of rest.

I found the hint, that the precompilation could help with the performance on startup. So I wanted to see myself, if this would help to get beyond theses HardDeadlineExceededError. Here's my story, how I got this flag enabled.

2. Modifications

There was no possibility, to enable precompilation in the app-engine plug-in version 0.8.5, so I had to find a way, to enable it. First, I included a new possible parameter to the _Events.groovy:

I inserted the two lines:

and

Lastly, I added the new parameter to the applications.properties file.

3. Results

The output while uploading was:

As far as I remember, I got a similar message as Building Blocks, but I couldn't reproduce them for my blog. The message above is all I got. Is it worth investigating...

Unfortunately, the response time for executing didn't increase much, if at all. I got the same impression as Buildung Blocks, but I feel that his methods of measuring are a bit more accurate.

4. Links

Google app-engine blog Building Blocks - app-engine precompilation

grails upload to Google's app-engine

1. Deployment

First, I created an app-engine-account.

Second, I put my account name into the Config.groovy:

I used the NetBeans context menu to deploy the applicaton: right-click on the project, "Run Grails Command...". Then I selected "set-version" and "1" as parameter and afterwards "app-engine" with the parameter "deploy". This creates the web application archive (WAR).

The plug-in documentation says, that you should execute $APPENGINE_HOME/bin/appcfg.sh update ./target/war from command-line. I left this point out, because my web-app seems to work even without it.

The following import process took a while. It asked for my Google-account and my password.

The prompt for the Google-account first needs one additional ENTER. The second prompt takes your Google-account.

2. Drawbacks on Google's app-engine

From time to time, accessing my web application takes a while. And sometimes, the request gets a timeout and a com.google.apphosting.runtime.HardDeadlineExceededError is thrown.

There seems to be some issues with the Home-link in my application. It always links to the current page, while it should link to the web-app-root.

There's also a problem with the information div. If it is set, it keeps the value accross different requests, while it usually should only be displayed once.

3. Links

app-engine plug-in and setup

my grails web-app with grails 1.2.0 and Google's app-engine 1.3.0

1. Motivation

It is almost for a year now, that I'm trying out Java EE. I figured out, that it could be pretty powerful and I was impressed by its possibilities. But I always found it a bit complicate. Particular in my After-Work-Programming-Environment, I got far too little things going.

So I concentrated on looking for a solution easier to use. Grails, which I read a lot about recently, seemed to me worth investigating. Some point that I found pretty intriguing:

• reuse of my Java knowledge
• handy groovy language
• use of (at least for me) new techniques such as dependency injection via built-in-Spring
• well known and approved programming environment NetBeans
• and at last a cheap and hopefully easy-to-use application server provided by Google's app-engine

2. Setting things up

Installing grails and the app-engine-api is plain sailing. First I was a bit concerned using grails 1.2.0 with Google's app-engine, cause I found some misleading information. But as you will see, there's no need to be concerned. Grails 1.2.0 runs on the app-engine.

Notably misleading was the information on top of the documentation to grails appEngine-plug-in that version 1.2 is not supported. Well, now the documentation is updated and there shouldn't be any confusion no more.

On my Ubuntu box, I installed NetBeans from catalog. It's not the most recent version 6.8, but I felt that using 6.7.1 should do it.

There is a nice plug-in for NetBeans supporting grails. It support the grails command and has some auto-completion functionality. Configure the plug-in in the Option -> Miscellaneous -> Groovy menu.

3. Hello World

First, I created a new project in NetBeans. So I chose File -> New Project ... Selected Category "Groovy" and Project "Grails". At last, I named the project "HelloWorld".

To make the app-engine-api available to grails, I edited the file BuildConfig.groovy since I couldn't get the env-variable APPENGINE_HOME to be seen by NetBeans. I added the following line:

Now I installed the app_engine plugin. To do so, I used the Netbeans context menu: right-click on the project, "Run Grails Command...". Then I selected "install-plugin" and "app-engine" as parameter. Version 0.8.5 was selected and installed. You are asked, if you want to install either JPA or JDO support. I decided to use JPA, since I haven't got any experience in JDO so far.

I'm fascinated by Grails GORM, so I wanted to take advantage of this features even when using the app-engine. There is a plug-in, making at least the get, save, delete commands available to the controller. So I installed the "gorm-jpa" plug-in via context menu. Version 0.6 was selected and installed.

There are some needs, to change the configuration, since you don't have access to the file-system when using app-engine. I changed the Config.groovy file:

I created my first domain class: HelloWorld.groovy. Be sure, that you add a package-name, since app-engine only accepts files within a package. A greate advantage of gorm-jpa is, that it enters some example JPA-annotation to the domain class.

The last thing was, that I created the controller classes via scaffolding. I just hit the context menu from the HelloWorld.groovy and hit "Generate all".

I tried to run the application using the Grails command "app-engine run" and visited http://localhost:8080/ to see the result.

4. Null-Pointer-Irritations

The result was, there was no result. The start-up of my app-engine-instance failed due to a weired NullPointerException.

I figured out, that it was caused by the underlying version of Spring. There was an exception in their GenericBeanFactoryAccessor.getBeansWithAnnotation-method, causing the JpaPluginSupport.groovy script to fail. Here's a solution: I copied the source code of GenericBeanFactoryAccessor to the file

I also edited the file

and pasted these lines to line 72:

I reran the "app-engine run" command and checked the localhost. This time, it ran like a charme!

5. Conclusion

The pretty tough part was to get beyond the NullPointerExceptions. The plug-ins seemed to me some kind of unready.

Besides, grails seems from this point the right choice:

• there is a pretty active community providing help and support,
• the gorm-jpa plugin makes database-activity pretty simple,
• scaffolding gives you a simple template for your web-application, and
• grails is so handy.

It will take further investigations. But so far, I give two thumbs up for Grails! :-)

6. Links

NullPointer Irritation solution