Jack of Everything; Master of Nothing

Gnome 3 Rant (and fix for modal dialogs)

2012-01-23T22:28:00.000Z

Back in the Gnome 2 days I used to love using Linux as my main developer machine but since Ubuntu and Gnome decided to build a mobile/cell phone interface for desktop PCs its gone back to the dark old days.

After Ubuntu switched to Unity I decided to jump ship to Linux Mint. Now Mint and most of the other big distros have gone Gnome 3, I'm left frustrated. These frustrations would be even worse if I'd chosen Fedora or SUSE as they don't offer the gnome shell extensions that make life bearable.

All the progress that was made with Gnome 2 and Compiz just seems to have been thrown away. The compiz-effects combined with Gnome 2 made for an awesome combination that rivalled anything I saw in Windows 7 or Mac OSX. They also kept me productive. Even Windows 8 will provide both a classic look and feel as well as there new Metro UI. With the exception of KDE based distros, Mint are the only distro that are trying to provide a choice to its consumers out of the box.

I don't know what has gone on with Nautilus but they have succeeded in making a newer version worse than the version that came before. However, that is a rant for another day.

Fortunately what I really like about Mint is that they know who the target market for Linux is/was: developers, administrators and tinkerers. These are the type of people who need (and can deal with) a multi-tasking OS that can open more than one window at a time.

The new modal dialogs (a "feature" of gnome 3) have been really annoying me lately so I went on a hunt to find out how I could revert back to the traditional style of dialog. You know they type of dialog with a window bar that allows you to move it around the screen. Seriously everyone I know can deal with this concept. Let the developer choose the intent!

The solution is simple: gconf-editor to the rescue. Just open gconf-editor and untick /desktop/gnome/shell/windows/attach_modal_dialogs. Press Alt + F2, type r and press Enter (reloads gnome shell). Now you have the normal behavior back.

GWT : DisclosurePanel Images, IE6 and SSL

2011-06-21T10:23:00.002Z

I recently worked on a project where we were using standard GWT Disclosure Panels and Tree components. When the application was sitting behind SSL we found that the arrow images were not visible in IE6. What was more frustrating was that in every other browser the images displayed correctly.

After a lot (and I mean a lot) of time I stumbled across the API documentation for image bundles which is the mechanism/technique Google have used to package up images for widgets into a composite image that help reduce calls to the server and speed up the user experience.

In summary, the problem is that GWT makes use an activeX control to process png transparency on IE6. Internet Explorer specifies that files which require the plugin for viewing must be cached to disk by the browser. By default the HTTP headers set by the GWT servlets when the image is requested specify that the payload should not be cached. The image then never stored to disk (cached) and the activeX control cannot process the image and display it to the page.

The solution is actually quite simple. You need to create an additional servlet that executes when image resources are requested. This is done by setting up a servlet in the web.xml with a file filter.

package xxxx.xxxxx.xxxxx;

public class ImageServlet extends HttpServlet {

 private boolean isIE6(final HttpServletRequest request){
  final String userAgent = ((HttpServletRequest) request).getHeader("User-Agent");
  return userAgent.contains("MSIE 6");
 }

 protected doGet(final HttpServletRequest request, final HttpServletResponse response) throws ServletException, IOException {

  if (isIE6(request) ){
   response.setHeader("Cache-Control", "");
   response.setHeader("Pragma", "" );

   // This should be some date in the future. Dynamically create the
   // date part of the string to be something in the future e.g.
   // sysdate + 1
   response.setHeader("Expires","Thu, 1 Jan 2013 00:00:00 ");
  }

  final String uri = request.getRequestURI();

  // Extract the icon name
  final String iconName = uri.substring(request.getContextPath().length(), uri.length() );

  // Supply the icon back to the browser
  continueProcessing(request, response, iconName);
 }

 protected void continueProcessing(final HttpServletRequest request
    , final HttpServletResponse response
    , final String iconName){

  final ServletContext context = getServletContext();
  final InputStream is  = context.getResourceAsStream(iconName);
  final OutputStream os = response.getOutputStream();
  response.setContentType("image/png");

  byte[] = new byte[8912];

  while (true){
   final int l = is.read(b);
   if (l < 0)
    break;
   os.write(b,0,l);
  }
 }
}

The servlet itself should look something like that below :


 ImageServlet
 xxxx.xxxxx.xxxxxx.ImageServlet


 ImageServlet
 *.png

You then need to set up the servlet mapping in your web.xml so that all png requests get routed through this new servlet. The example could be improved by adding caching of images requested. This would also speed up load performance of your application for images that are requested more than once.

JBOSS, GWT and Request Factory : Unknown Entity Exception

2011-06-04T01:43:00.000Z

I've been playing around with RequestFactory, GWT's data centric transport/serialization mechanism and was really struggling to set up a new project that could pull data from an existing set of database tables and I wanted to share my experiences if you decide to use an existing schema.

The tutorial can be found over on the GWT developer documentation and I don't want repeat what can be found there.

I decided to use an Oracle XE database and the world famous HR sample database, creating JPA entities for the EMPLOYEES and DEPARTMENTS tables.

JPA/Hibernate benefits from having a version column that is updated each time the object is persisted. RequestFactory uses this to determine whether changes have happened on the entity.

The first problem I encountered was that I kept seeing "Unknown Entity Exception" in the server logs (JBoss-6.0.0-Final). This was eventually solved by ensuring that the employees table had a "VERSION" column of type NUMBER(38). The annotated JPA class also requires the type to be of type Integer (Use the boxed/wrapper type and not int).

Initially I tried using Long for the "VERSION" column however that produces more errors in the logs and it was quickly evident this needs to be set to an Integer to be compatible with the @Version annotation.

Below is my entity definition that maps to the employees table.

@Entity
@Table(name = "EMPLOYEES", schema = "HR")
public class Employees implements Serializable {

 private static final long serialVersionUID = 1717056757381711656L;
 private static final Logger logger = Logger.getLogger(Employees.class);

 @Id
 @Column(name = "EMPLOYEE_ID")
 @NotNull
 private Long id;

 @Column(name = "VERSION")
 @Version
 private Integer version;

 @Column(name = "FIRST_NAME")
 private String firstName;

 @Column(name = "LAST_NAME")
 private String lastName;

 public Long getId() { return id; }

 public void setId(Long id) { this.id = id; }
 
 public Integer getVersion() { return version; }

 public void setVersion(Integer version) {this.version = version; }
.....
}

Finally you'll need to ensure that all rows in your schema are initialized with a default version. A simple update statement solves this:

update EMPLOYEE set version = 0;

While trying to solve this problem google searches led me down the garden path suggesting that one checks the JPA annotated class imports javax.persistence.Entity and not org.hibernate.persistence.Entity. Other searches suggested it was a incorrect configuration when using non-container based persistence. This was also not the problem as I was using a JTA datasource. The configuration for which is below.


  
    hrDS
    jdbc:oracle:thin:@localhost:1521:xe
    oracle.jdbc.driver.OracleDriver
    HR
    password
    org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
      
         Oracle9i

My persistence.xml file was as follows:


    
        org.hibernate.ejb.HibernatePersistence
        java:hrDS

Again hope that helps someone else out there.

Accessing BLOBs and Servlets, Serving Images Stored in H2

2011-01-24T22:29:00.001Z

In a previous post I looked at how to store images in a H2 database using groovy. The example served as nothing more than a little bit of fun. I just wondered how you'd go about doing it and wanted to explore how to get this done in groovy and H2, so wrote a script to load some images into the database.

After this I thought well how can we make use of these from a web application. In this post I'll take a look at how to serve up images through a servlet so that we can display them in a web page. I was more interested in the perceived speed of such a technique over storing the file on the file system and I must say it actually didn't feel that slow.

The code is as follows:

public class GetImage extends HttpServlet {

 private static final long serialVersionUID = -3505939293849012116L;

 protected void doGet(HttpServletRequest request, HttpServletResponse response) 
       throws ServletException, IOException {

  String beerId = com.google.common.base.Preconditions.checkNotNull(request.getParameter("id"));

  InitialContext ctx;
  DataSource ds = null;
  Connection connection = null;
  PreparedStatement stmt = null;

  try {
   ctx = new InitialContext();
   ds = (DataSource) ctx.lookup("java:/beerDS");
   connection = ds.getConnection();

   String sql = "select image1, image1_content_type " +
     "from beer where id = ? and rownum() = 1";

   stmt = connection.prepareStatement(sql);

   stmt.setInt(1, Integer.valueOf(beerId));
   stmt.execute();

   ResultSet rs = stmt.getResultSet();

   while (rs.next()) {
    byte[] bytes = rs.getBytes("image1");
    String contentType = rs.getString("image1_content_type");
    int length = bytes.length;
    response.setContentType(contentType);
    response.setContentLength(length);
    response.getOutputStream().write(bytes);
   }
   response.getOutputStream().flush();
   response.getOutputStream().close();

  } catch (NamingException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (NullPointerException npe) {
   return;
  } finally {

   if (connection != null)
    try {
     connection.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   if (stmt != null)
    try {
     stmt.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
  }
 }
}

A few things to note include:

You can't write multiple images to the response, not if you wan't something that can be understood by a browser. You refer to the servlet like any other file when using the url html or dynamic content. To access the image in a html page you would use a standard img tag as follows, like in the following example :

For the link to work, you'll need an entry in your web.xml (or Guice server module configuration - which is a really kool way to get rid of web.xml entirely) that ensures theres a mapping between "/my/*" url pattern and the GetImage servlet.
The underlying example uses my beer database configured as a data source as described in a this post
You'll need to ensure there is a blob in the database for the beer with ID 22 in the beer table. See my groovy script example that looks at how to get images into the database.

Configuring a H2 Data Source in JBOSS 6

2011-01-23T19:05:00.005Z

In this post I'll look at setting up a data source for JBOSS so that you can use a JNDI lookup to connect to your database from your web application. It's really easy and takes no time at all. JBOSS includes some example datasource configurations under JBOSS_HOME/docs/examples/jca that you can copy and edit for your own purposes.

I copied the hsqldb-ds.xml to my server profile under JBOSS_HOME/server/all/deploy and edited it as follows :



   
      beerDS
      jdbc:h2:tcp://localhost/~/BEER

      org.h2.jdbcx.JdbcDataSource
      sa
      

      
      5
      20

      0
      32

H2 is compatible with the HSQLDB drivers but they recommend using the H2 driver so thats the first thing I changed.

org.h2.jdbcx.JdbcDataSource

I then changed the connection url and specified my database, “BEER” in this case.

jdbc:h2:tcp://localhost/~/BEER

The configuration shown also sets up a connection pool which should help optimise performance.

Before you can use H2 from within your web application you need to make sure the H2*.jar is on the classpath. I copied my h2*.jar to JBOSS_HOME/common/lib.

Ensure your database is started and use the following code to get a handle to a connection you can then work with in your servlet code:

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/beerDS");
Connection connection = ds.getConnection();

You can then use the connection to query the database using sql or use JPA persistence and the JDNI name java:/beerDS.

These examples use my Beer database I set up in the previous post. This script will allow you to get a H2 sample database up and running. See my previous post for more details

BLOBS with Groovy and H2 [or your database of choice – Oracle, MySQL]

2011-01-23T00:58:00.009Z

I am slowly building a couple of environments and set of applications that will allow me to explore techniques, design patterns (as-well as re-learn those forgotten ones) and test drive shiny new technologies like GWT and Grails. More of a reference set for myself than anything else.

As part of this I've needed to set up a database and application server. I decided on H2 as I feel it might be useful for some other pet projects I have in mind. I grabbed a free dataset from www.freebase.com who provide open source data-sets. I massaged the structure and normalised it into a couple of tables and added the appropriate primary keys. I chose a dataset that most people could relate to, beer.

Just for fun I thought I would try storing logo's against a given beer inside the database to keep everything self contained and to see how H2 performed.

Having dipped in and out of groovy and taking time to learn the rudimentary basics I haven't found much of an opportunity to use it and it seemed a nice fit for
this as I can imagine it is something you might want to script.

The code is as follows :

import groovy.sql.Sql

println "---- A working test of writing a blob into a H2 DB ----";

sql = Sql.newInstance("jdbc:h2:tcp://localhost/~/BEER;MAX_LENGTH_INPLACE_LOB=4096;COMPRESS_LOB=LZF ", "sa","", "org.h2.Driver" );

try {
 File f = new File("/tmp/stella.jpg");
 InputStream fis = new FileInputStream(f);

 def image_type = "image/jpeg";
 def updateStatement = "update beer set image1 = ? , image1_content_type = ? where id = ? ";
 def ps = sql.connection.prepareStatement(updateStatement);
 
// Set the BLOB with the FileInputStream
 ps.setBinaryStream(1, fis, (int)f.length());
 ps.setObject(2, image_type);
 ps.setObject(3, "22")  // Pilsner - 926, Stella - 22
 ps.execute()

 sql.commit()
} catch (Exception e) {
 println "Failed: $e"
 sql.rollback()
} finally {
 sql.connection.close()
}
println("finished");

The uploaded file has some extra comments explaining whats happening but there is a few things to note:

You can swap the jdbc url and h2 driver for those of your own database
You need to ensure the jdbc url is correct
The jdbc drivers must be on the class path
The jdbc url here specifies the H2 optimisations for working with LOBs
It is a good idea to store the mime-type in case we decide to serve this up over the web later.
You'll need a copy of my database for this to work beer db

The sql script was a direct export of my database so you should just be able to run it from the standard web interface that comes with H2 when you start it. You can start H2 by double clicking on the H2*.jar if you have jars associated with the Java 6 runtime.

To run it should be as simple as :

$ groovy -cp "/path/to/h2-XXXXX.jar" write-blob.groovy

Files

Java Databases

2011-01-22T23:53:00.001Z

I've been playing around with some database engines based purely on Java recently (H2, HSQLDB and Apache Derby) and I've been pleasantly surprised. The one I'd like to concentrate on here is H2.

It lists an impressive set of features especially compared to other databases. There is also a more detailed comparison on the H2 website

However what I most like is its size. The whole distribution is less than 1.2 MB and that includes a reasonably sophisticated web front end for running queries and inspecting the databases. Its also seems incredibly fast (considering its written in Java) and especially so when compared to Derby though I only have anecdotal evidence for this from my experience building a couple of sample projects.

If you haven't tried it already, I really recommend giving it a go as its incredibly easy to get applications up and running with especially in Java.

GWT and JRE managed persistence (JPA) with Apache Derby

2011-01-13T21:47:00.017Z

A while back I was playing with JPA/EJB3 persistence and GWT (Google Web Toolkit) but didn't want to use a heavy weight Application Server . The idea behind the application I was looking at was that it should be distributable and always run locally.

I downloaded the hibernate and JPA annotations from the hibernate website and thought it would be a simple case of configuring the persistence.xml file. After a some difficulties getting my application to hit the test Derby database I turned to google and it seems others had problems too.

I was expecting to have to create the persistence.xml file under “war/META-INF/” in my eclipse project. This is just wrong, it seems the persistence.xml file has to be on the classpath when your not using container based persistence.

The next problem I encountered was that I'd not included all the necessary jars from the hibernate distribution. The following combination seemed to work for me in the end.

WEB-INF/lib/

hibernate3.jar
hibernate-jpa-2.0-api.1.0.0.Final.jar
javassist-3.12.0.GA.jar
jta-1.1.jar
slf3j-api-1.6.1.jar

My persistence.xml was as follows:

<persistence xmlns="http://java.sun.com/xml/ns/persistence" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd" version="1.0">
  <persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
   <provider>org.hibernate.ejb.HibernatePersistence</provider>  
  <properties>  
   <property name="hibernate.archive.autodetection" value="class" />  
   <property name="hibernate.format_sql" value="true" />  
   <property name="hibernate.show_sql" value="true" />  
   <!-- <property name="hibernate.hbm2ddl.auto" value="create-drop" />-->  
   <property name="hibernate.connection.url" value="jdbc:derby://localhost:1527/slicr;" />  
   <property name="hibernate.connection.driver_class" value="org.apache.derby.jdbc.ClientDriver" />  
   <property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect" />  
   <!-- <property name="hibernate.connection.username" value="user" />  
   <property name="hibernate.connection.password" value="password" /> -->  
  </properties>    
  </persistence-unit>
</persistence>

I was only knocking up a quick and dirty application as a learning exercise but spent a good couple of hours just trying to hit the database. Hopefully this will help others in the same situation.

I'm back (again)

2011-01-12T20:47:00.000Z

I had intentions of developing my blog back in 2006 and managed to write a few half decent articles. Well its now 2011 and I’ve decided to take up blogging again.

My career has shifted more towards Java and OO Development and I’m no longer working so closely with Application Express but maintain an active interest in the good work the folks at Oracle are doing. My primary interest for blogging is to stop me forgetting some of that important stuff you come across when coding, hopefully someone out there will find this stuff useful too.

I’m currently playing with GWT, JBoss, Groovy, Grails and all things open source with a little Oracle and Weblogic thrown in there for good measure so hopefully they’ll be more to come from me on those technologies.

I use Ubuntu, But Why?

2007-11-21T10:36:00.001Z

Those that know me well, will know that I am a desktop Linux advocate and that I completely moved my home desktop to Ubuntu after considering what I really need my computer to do. The list below is a pretty comprehensive list of what I want to be able to do with my computer:

Run in a timely manner
Not crash when something/anything throws a wobbly
Internet Access (General Web Surfing)
Internet Chat (MSN, Skype)
Word Processing, Spreadsheets
Storing / Editing Photos from my digital camera
Printing Documents
Scanning the odd document or photo
Playing music (MP3s, Internet Radio)
Playing Videos/DVDs
Burning CDs/DVDs
Simple Development & Editing Text Files

For me the switch was not about the politics of "free software vs. the almighty evil other company or proprietary software" but the fact I don't need or want a server farm to run the latest version of the software, have a bloated desktop that takes 10 minues to “load my personal settings”, have to upgrade my PC every 6 months or update my virus definitions religiously.

I migrated to Ubuntu just after Vista was released, after hearing and having a few negative experiences with Vista on other people’s hardware. For me, moving to Vista would have meant that I would need some new hardware. My home PC has a fairly modest specification consisting of Athlon XP 2000 (1.67 GHz Processor), 1GB RAM, 64 MB RAM NVIDIA Geforce 2 Graphics card, and 2 x 250gig hard drives. I could have moved to Mac but that would have meant shelling out at least £600 on hardware (something I might still consider when my current hardware starts to feel slow).

I’ve been using Ubuntu on my home servers since the “Badger” but never really felt that it was quite ready for my desktop, until Feisty. I took the plunge after weighing up the pros/cons and trying the live CD with my hardware. Do I regret it? Definitely not. For years I’d run Linux in a VM, now it is the other way round and I am running Windows in a VM for all those legacy applications that must use Windows.

The first thing that hit me was how fast my system goes on such a modest specification. Put simply, every aspect of my computing needs in the list above is covered by Ubuntu. I’ve yet to encounter a task which I can’t accomplish in Ubuntu. For me the benefits include “less” worry about mal-ware, viruses and my general security, performance and the total cost of my home computing experience is much less (More Free-Software/Free-To-Use alternatives and less hardware upgrades).

There are a few minor niggles such as driver compatibility, wireless connectivity and lack of commercial software. However, I am lucky enough to own a scanner (Packard Bell 2400), wireless card (Not sure of model, it just worked) and printers (Samsung ML-1210, Samsung ML-4550) that are ALL supported under Linux. Others might not be so lucky. The fact I am NOT a gamer also helps. The lack of commercial software refers to the unavailability of commercial software like Photoshop and Fireworks. These are pieces of software I’d be happy to pay for if they were available. Though I believe this will change over time as more and more people are starting to use Desktop specific versions of Linux such as Ubuntu. Free-to-use software such as Skype, Opera and RealPlayer are already available and have been for some time.

The simple truth is that if Windows was as performant and secure as my Linux Desktop I would have probably stuck with Windows as it does just work with most hardware.

My advice to anyone looking to buy a new PC would be to try Ubuntu first. You can download the live CD from the Ubuntu website and try it without installing it. Its also the first real distribution that doesn’t require extensive Linux or computing knowledge. You can install it and use it.

Ubuntu Website -> http://www.ubuntulinux.org
Ubuntu Forums -> http://www.ubuntuforums.org
Dell computers with Ubuntu -> http://www.dell.com/ubuntu

Clear Page Cache When Using Tabs & Sub-tabs (new solution)

2007-11-14T11:48:00.001Z

Previously I posted a solution for clearing page cache of a target page when a user clicks a sub-tab. While my solution works there is much cleaner way of achieving this involving the use of APEX_UTIL.CLEAR_PAGE_CACHE().

Using the same scenario, consider that we need to clear the page cache of the page that we will be re-directed to, before it loads. The solution is very similar but requires no modification of the target page.

Firstly, create a new application page process:

Navigate to Applications -> Shared Components -> Application Processes and hit “Create” in the application builder. Create a process which runs “On Submit: After Page Submission – Before Computations and Validations”. In Process Source type the following

IF (:REQUEST = 'T_YOUR_TAB_NAME’) THEN
APEX_UTIL.CLEAR_PAGE_CACHE(your_page_number);
END IF;

T_YOUR_TAB_NAME can be identified from the developer mode or using firebug in Firefox on the specific tab your interested in. That's it! Now when a user clicks on a sub-tab the target page's, page cache will be cleared before it loads. To clear the cache for another tab, just add a new block to the application process you have created.

2007-11-14T10:58:00.000Z

Apex Gotchas

While Apex is an intuitive easy to use development environment it can cause some frustrations and has a number of gotchas that are worth been aware of. The following are a few I've discovered over the last couple of months.

Validations of type PL/SQL Expression without conditions

This little beauty caused me an hour and half worth of grief.

Set up a pl/sql validation of type pl/sql expression, give it a name but leave the condition entry blank.

When you run the page you will see “Invalid PL/SQL Expression” at the top of page before the header and rest of your page.

POP LOV, display description return value returns “undefined”

If you have a page item (Select List), which uses an LOV query, and have enabled null columns (but not specified a null value) you can typically expect the value in the session state to be “%null%” after the page has been submitted.

However it appears that if you have a “Popup LOV, display description, return value” item then the null value actually gets submitted to the session state as “undefined” and not “%null%”. Although this one is easier to spot it more of an annoyance and just appears odd as it actually returns “undefined” to the display area too.

Setting Page Items in Validations, Report Regions and PL/SQL Function Body Conditions

Have you ever worked on a bit of functionality where you're effectively hacking it to get it to work with the good intention of re-factoring it later and make it look pretty?Well on one occasion where I had forgot to go back and re-factor a particular page I had worked on I discovered that I had set a page item within a validation.

Logically thinking, I personally would not expect to see a page item set in a validation (your circumstances may be different). Apex allows you to make an assignment to a page item anywhere you can place PL/SQL blocks (E.g. report regions returning SQL Queries, item/region conditions (I.E. function body returning boolean), LOV Queries. Generally speaking I'd expect such a function to be carried out in process. The old saying , “just because you can doesn't mean you should” comes to mind.

I feel this is a good example demonstrates the need for standards and consistency when developing Apex Applications if not for your own sake then for the poor person who is going to support your application once your gone. More about good practice in a later post.

I did have one more gotcha, concerning dates but can't remember the specifics, if I do I'll add it to the list, likewise if anyone has any more please feel free to add them to the comments section and I'll update the post.

(The gotchas may not apply to versions of Apex greater than 2.2.1 unfortunately the project I was working on was at that particular version)

2007-11-14T10:53:00.000Z

Ending the Silence

It has been well over 3 months since my last post and I know its lame but “I just have not had the time”.

Since then I’ve been coding and documenting as my long stint at my last client came to an end last week. After a hard day at the office, touching a computer is the last thing I’ve wanted to do. However my blog-topic list has got ever longer where I’ve jotted down idea but not had the time to follow them up and there has been some exciting developments on the Oracle Landscape.

So if you a regular reader, fear not, I am still alive and there will be more posts coming over the next couple of weeks.

2007-07-12T10:58:00.000Z

Oracle 11g is here

Yesterday saw the launch of Oracle 11g. Eager to get my hands on a copy I popped over to OTN but was disappointed to find that its not yet available for download. However there are a stack of white papers looking at the new features. You can access them here: http://www.oracle.com/technology/products/database/oracle11g/index.html Hopefully it will be made available soon. I'm keen to try out the native binary XML support as well as further improved flash back features.

2007-07-06T08:26:00.000Z

Clear Page Cache When Using Tabs & Sub-tabs

Update - 14th Nov 2007 : I have posted a cleaner solution to this at http://kristianjones.blogspot.com/2007/11/clear-page-cache-when-using-tabs-sub.html Most pages you create in Apex have a number of modes that govern when processes, items and regions are displayed, executed or evaluated. Typically this involves setting a hidden page item and testing your condition against that item in the “Conditional Display” pane within the developer mode. When developing applications with tabs (and sub tabs) you’ll discover that you cant process the request value for your tab as Apex manages the branch conditions for you. An example scenario may be where you want to clear the page cache only on new requests. One method to get round the problem is to create a hidden item on page 0 and called “P0_PAGE_NEW” Navigate to Applications -> Shared Components -> Application Processes and hit “create” in the application builder. Create a process which runs “On Submit: After Page Submission – Before Computations and Validations”. In Process Source Type the following

IF (:REQUEST = 'T_YOUR_TAB_NAME’) THEN
:P0_PAGE_NEW := 'Y';
END IF;

Now you can create a “On Load – Before Header” session state page process to clear the session that is conditional on :P0_PAGE_NEW being set to ‘Y’. Once you’ve cleared the page cache, just set :P0_PAGE_NEW to ‘N’ in a “On Load – Before Header” pl/sql page process which runs unconditionally with the following source:

begin
:P0_PAGE_NEW := 'N';
end ;

The cache should now be cleared with every new page visit. Now imagine, you have one or more tabs which point to the same page. Ideally you would like to have each tab a cause a different action or set of items to be displayed (hypothetically). To do this you can just add to the application process you have created but instead you would set you page mode type item on the page. From there it is a simple matter of conditioning your processes, items and regions to executed or be displayed against that page mode item

2007-06-19T14:47:00.000Z

Create table as select for LOV generation

If your creating Apex applications on a regular basis then sooner or later you will have made use of LOVs. Recently I needed to create an lov using the data contained within USER_TAB_COLS as a starting point. A feature of Oracle DDL allows you to create and populate a table from a sql query. For example

create table test_lov_table as
    init_caps(replace(COLUMN_NAME,'_', '  ')) COLUMN_NAME
    ,CHAR_LENGTH
from user_tab_cols
where TABLE_NAME = 'my_table';

The above code only serves as an example but got me thinking whether this technique could be useful anywhere else. Purely speculating I thought that this could be something to consider if your migrating data from older systems and need to take portions of your data model to create reference data tables for LOVs. Another purely hypothetical situation where this may speed things up is where you need to map one schema's tables, columns to another's tables and columns (through the respective user_tab_cols table) i.e for the purpose of interfacing two data models again.

Call Java Methods from ApEx

2007-05-16T14:37:00.002Z

For those new to ApEx it may not be immediately obvious that it is possible to execute Java Methods from ApEx. Newbies to Oracle database programming may also be surprised to know that you can actually embed Java in the database at all! The fact that this is possible make the Oracle Database Server very powerful making almost everything possible. If you cant code it in PL/SQL then try Java, If you can't use Java then perhaps you are doing something wrong (or need to go back to basics).

Why would you want to call Java from ApEx applications?

PL/SQL is great but there are occasions when you may need to perform complex processing that simply is not possible with PL/SQL, is too complex to implement or requires the use of third party APIs. One such example may be an image conversion process, which converts a GIF to a JPEG programmatically (granted I know this is possible with Oracle Intermedia).

As regular readers know I’m currently involved in a piece of work which requires various third party Java APIs which can strip out user entered data from a PDF to XML. We then process this relationally using good old pl/sql and relational views over xml. Apologies to those who thought this post might be looking at that topic, I am still writing it up and need to prepare suitable example, which can be downloaded and played with, so please bear with me.

Pre-requisites

Oracle Database with Java Support ( See Product Matrix )
A DB schema with relevant schema privileges to execute Java.
ApEx (pretty much any version will do)

How do you do it?

Calling Java from the Database is a fairly trivial task typically consisting of the following 3 steps.

Writing the Java Classes
Loading the classes/code into a target database schema
Creating a PL/SQL Wrapper round the Java Method you wish to call.

If you need to load pre-compiled java classes into your schema you can use a very useful tool called loadjava. You will need to use this utility if you have existing JAR files and APIs. Hopefully I’ll explore loadjava in a later post, for the moment I would like to concentrate on demonstrating a very simple example of calling a Java method from within Apex. I’m afraid that means it is a “hello world” example as it does not require any pre-compiled classes to be loaded into the database.

Step 1. Create Java Class

The following code creates and compiles a java class called HelloWorldExample. Notice that any method you wish to call from pl/sql must be declared as static. You can copy and paste the following code into sqlplus or sqldeveloper.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "HelloWorldExample"
AS
public class HelloWorldExample
{
HelloWorldExample hello;

public HelloWorldExample()
{
hello = new HelloWorldExample();
}
public static void sayHello(String forename, String surname
                      , String outString[])
{
outString[0] = "HELLO WORLD more specifically hello" + forename
          + " " + surname;
}
public static void main(String[] args)
{
//no method
}
};
/
show errors java source "HelloWorldExample";
/

You should see :

AND COMPILE Compiled.
No Errors.

Step 2: Create a pl/sql call spec/wrapper

In order to use your Java method from Apex or PL/SQL you must create a pl/sql call specification wrapper round each Java method you wish to run from PL/SQL.

Again copy and past the following code into sqlplus/sqldeveloper:

CREATE OR REPLACE PROCEDURE HELLO_WORLD(p1_forename in VARCHAR2
, p2_surname in varchar2, p3_outString out varchar2)
AUTHID CURRENT_USER AS LANGUAGE
JAVA NAME  'HelloWorldExample.sayHello(java.lang.String
, java.lang.String , java.lang.String[])';
/
show err;
/

Step 3: Execute to test

Executing this Hello World example is as easy as declaring an anonymous block and calling the method from within within. For example:

declare
p_forename varchar2(4000) := 'KRISTIAN';
p_surname  varchar2(4000) := 'JONES';
p_msg      varchar2(4000) := null;
begin
hello_world(p_forename, p_surname, p_msg);
dbms_output.put_line(p_msg);
end;

Again if completed correctly you will see :

Anonymous block completed
HELLO WORLD more specifically hello KRISTIAN JONES

Hopefully this has served as a gentle introduction into embedding Java classes in the database. You can call this from procedure from ApEx page as a pl/sql process just substitute in your page items. If anyone has any queries please leave a comment. I'll do my best to reply.

2007-05-02T15:53:00.000Z

ApEx - BRANCH_TO_PAGE_ACCEPT

A colleague who also works for Oracle Consulting has recently blogged about a very useful and not so well known feature of ApEx. BRANCH_TO_PAGE_ACCEPT is a special type of request value which basically allows you to branch to a page, passing in a page item value, skip the page rendering and goes straight to page processing. Effectively this feature allows you to submit a page with a number of page item values to another page (where the processing functions are located) without the user actually visiting the page through interaction. The post can be found here: http://atulley.wordpress.com/2007/04/30/branch_to_page_accept/ Although I can think of a number of potential uses for this I don't want to steal Andy's thunder. So I suggest you check his blog from time to time, he has some excellent stuff on there.

2007-04-27T08:44:00.000Z

Oracle FAQs

After recently reading a post on Tom Kyte's blog I stumbled across an excellent resource which is quoted by another fellow reader of Tom's blog. The Oracle Co-operative FAQ was set-up by Jonathan Lewis and is an excellent resource for anyone who uses the Oracle Database on a daily basis as a developer, tuner or DBA. I have heard people claim that the Oracle Database is bloated. My standard response is normally, "What you mean to say is that it has features you have not used or don't know how to use". Taking the time to scan the FAQ index may well enlighten you on a few extra features/techniques you can add to your portfolio making you more proficient. It's certainly worth a look. Enjoy Kris

2007-04-26T11:40:00.001Z

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

I would hate to think of the number of times I have been faced with an "ORA-01403 - No Data Found" or a "ORA-06502: PL/SQL: numeric or value error: character to number conversion" error and not known where its come from. In Java/C# you can simply make a call to the printStackTrace() method.In comparison I have always found debugging pl/sql a bit of a black art. I've known of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for some time but forgot of its existence until recently where I was faced with an exception raise in a pl/sql package but couldn't locate where error originated from. Example usage is as follows:

When Others Then
          p_msg_id := 0001;
          p_msg_type := 'E';
          p_msg_text :=
              To_char (Substr (Sqlcode, 1, 20)) ||
              ' : ' || Substr (Sqlerrm, 1, 200) ||
              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

Steven Feuerstein has a very nice article published on OTN and in Oracle Magazine (March/April 05). It can be found at http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

2007-03-14T15:38:00.001Z

OC4J or Java Stored Procedures

Some weeks ago I alluded that I would post a follow up to my Java Dilemma once I was back on site.

During this time I have been studiously working on a solution which allows a user to complete a computerized pdf form and submit it the server where a neat bit of code strips out all the user responses from a pdf form as XML. This in turn is then transformed to make it more useful to the system as a whole and stored in relational tables. This process is a legacy requirement which compliments a new online, web-based, data input system written entirely in Oracle Apex. Ideally it would be nice to stipulate that all users complete the form via the web-based front-end. Unfortunately not all the users have access to the system but still need to get their information into the system easily, hence the need for an offline form. The architecture employed is a typical Oracle Stack utilizing DB 10gR2, Apex 2.2, and AS/OC4J 10.1.2 with SSO. Architecturally we decided that it would be best to put all the Java Processing in a container on the web tier and write some custom JSPs to handle form upload, user response extraction and xml insertion into the database. As shown in Figure 1. Figure 1: The solution makes use of the Adobe XML/PDF Access API for Java or XPAAJ for short. Without the XPAAJ APIs this functionality would not be possible (easily). Unfortunately XPAAJ requires the Apache Xcerses and Xalan XML parser APIs. A suspected bug with Application Server 10.1.2 forces the use of the Oracle XML parsers as default and the Container class-path cannot be changed easily (in a supported manner). In Application Server 10.1.3 there is a very nice and easy way to accomplish this in a supported manner. This process is outlined on otn. AS/OC4J 10.1.3 was not an option for us and it meant we had to go back to the drawing board and use Java Stored Procedures. This approach is shown in Figure 2. However, this has given a very clear advantage. The Java procedures can be very easily used from within ApEx. This removes the need for writing tedious JSPs to manage the file upload process and instead allows facilitates the use of a simple ApEx file browser page item. With a pl/sql wrapper round the Java, the functionality is exposed seamlessly to the ApEx page just like any other pl/sql procedure. As the rest of the application we are writing is in Apex this keeps things very simple indeed. Figure 2: I'll be writing a simple how to which does a complete walk through once I have the time. Otherwise I hope some of the difficulties I've experienced with 10.1.2 will save someone some time.

2007-02-02T15:04:00.001Z

Un-deleting Files on Linux

Ever deleted a file from a terminal session in linux and wished you hadn't. Yeah, its happened to me too. Good news, if your using ext2, or ext3 as your file system and you set your system up properly. I.E you created separate partitions for each mount point, e.g. /, /home, /opt etc. You need to just unmount the volume where the file was located e.g. /home and use Midnight Commander (mc) to get your file back. mc comes standard with most distributions but if it doesn't go to http://www.gnome.org/mc/ and grab a copy. First unmount the volume eg.

umount /dev/sdd1

Inside mc, type:

cd undel:/dev/sdd1

You can't do this anywhere else but mc. Using the "cd" command in mc normally does what it would at a shell prompt; it changes directories. This special syntax instructs mc to display all the undeleted files on that partition instead of the files in a directory.

Wait a moment while it searches through that ext2 filesystem for delete inodes (an inode holds the _contents_ of a file, but not the directory name, etc.). In a minute or so, you'll see a list of files with names like "23434632:2" in that window. The dates and times for the entries are the dates and times when that inode was deleted. I find it most useful to sort this window according to time:

[F9], r, s, m, [Enter]

[F9], l, s, m, [Enter]

You can use the [F3] "View" feature to look at the contents of the inode. Press on top of the files that have times around the time you think you deleted the file(s). This tags them to be undeleted in a moment. In the other window (use [tab] to switch windows in mc), make an empty directory under /tmp, such as /tmp/deletedfiles.

mkdir /tmp/deletedfiles

Now switch back to your undel window and press [F5] to copy those files to your real filesystem. If you're done, you can leave mc with the key - see the legend at the bottom of the screen.

This would also be a good time to remount the /home partition with

mount /home

At this point you'll probably want to look at each one and decide what the real name should be. Recovered files should now show in /tmp/deletedfiles. Interact with these as you would any other file. Adapted from an article found here

2007-01-20T16:01:00.000Z

Java on top of vs Java inside Oracle

Since finishing my last project I have had some time on the 'bench', giving me the perfect opportunity to get some training in and up to speed for my next project, again with the same client (I must be doing something right). The project is going to predominately involve Database 10g, Apps Server 10g, Java and ApEx. As I mentioned in a previous post my development environment of choice at the moment is Oracle XE, due to its small memory footprint (comparatively). It provides most the features I've needed for projects I've worked on thus far. Furthermore, the prospect of installing 10g Enterprise is a task that doesn't sit too well, as I've known it to be a bit of a mission in the past. Unfortunately as I have become more experienced, I've discovered that XE lacks certain features that I would sorely like to make use of in my next Project. One such feature is Java Stored Procedures. After some digging around and posts on the Oracle Forums. Another user pointed me in the direction of this matrix which lists features available between the different versions, from XE to Enterprise. I need to write some Java which basically manipulates a PDF form which users will fill out and upload to the system we're developing. The Java code will then strip these responses out and store them in the appropriate database fields. Technically I can see two possible ways of accomplishing this, through:

Java Stored Procedures
Use of Java Containers/Extensions outside the DBMS. I.E a java app on the App Server, OC4J etc.

However I am unsure as to which is the best way to proceed. I would like to use Java Stored Procedures, as it is a process I think will re-occur time and time again. The process will also be heavily tied to an ApEx application. This makes calling a Java Stored Procedure fairly trivial. The solution would also appear to integrate better. I can however see some potential problems if/when the database version is upgraded. For instance will the code be compatible with the Java Environment shipped with 11g (or subsequent database versions)? At present 10g is J2EE 1.4.2 compliant but I have no idea what version of Java 11g will comply to. At present I don't even know whether this matters. Either way I think its time to move to a more enterprise like development environment on my laptop for when I'm on site. Without wanting to make a hash of my Windows Installation, I've opted for creating a VM instance running on Oracle Enterprise Linux. Hopefully I'll remember to post an update on my Java dilemma and let you know which way we opt for.

Extra leave for British workers...

2007-01-16T13:56:00.001Z

If anyone has been watching the news lately, you'll have noticed that some of us will be gaining an extra 8 days on top of our normal holiday allowance. The government has decided that companies can't include bank holidays as part of our holiday allowance.

The BBC have placed an article on their website if any of you happened to miss the news.

Securing Oracle XE and ApEx Apps

2007-01-15T12:20:00.001Z

As some of you know, I'm fast becoming an ApEx convert and I've started to use it to knock together quick and dirty applications which make my daily work life as a consultant easier. Rather than installing a full Oracle DB instance on my laptop I've opted to install Oracle XE which comes with ApEx 2.1 and is basically a stripped down version of Oracle 10g Database. It suits my purposes and is not as resource hungry as the complete Oracle Instance.

However XE is not without its problems. I knew there would be potential security risks and other considerations when developing in XE rather than a later version of ApEx and a fully patched 10g.

I recently discovered a fantastic security article written by red-database-security. The article highlights the flaws with XE and how to overcome them and is well worth a read if your using XE for production systems.

2007-01-09T00:59:00.000Z

I had the pleasure of test driving the new TT on Wednesday I don't think words can describe the fun I had in it. Put simply I'll be ordering mine at the end of the month. I can see why there is a six month waiting list though; The car was just phenomenal in every respect. It's comfortable, not too fuel hungry, turns heads and the acceleration was great. What more could you ask for. Briefly on the Acceleration the car just throws you back in to the seats and goes, and goes and goes. Even in 6th gear it flies. Vorsprung durch Tecknik indeed!

FlashFiler (NexusDB) to Oracle Migration

2006-12-18T09:43:00.005Z

The project I'm currently working on has required me to migrate data contained within Flash Filer2 database files (*.FF2). Although this is an old and obscure file format, others out there may well need to migrate data stored in such files to a newer, shiner DB, like Oracle.

We were supplied with a CD that contained a catalogue of data but unfortunately it was in this obscure FF2 file format. There was a front end to the data on the CD but this was pretty much useless as we needed to exploit the data in the application we were developing. After identifying the file format, the question was how to actually get at the data and make use of it in the new application. The client owned the data so copyright etc wasn't an issue, but it may be for you.

Like most databases, the flash filer database files take the form of binary file. My novel approach was to build a database instance round the files and accessing the data through a DB driver or through ODBC.

This meant there were 2 problems.

Getting hold of and installing the Database engine cheaply
Finding an ODBC driver for the database engine

I sniffed failure when I discovered the company who had produced the database engine had ceased trading. After much googling, I found that the Flash Filer DB engine had been kindly placed on source forge at http://sourceforge.net/projects/tpflashfiler and some instructions at http://www.atari-source.com/docs/turbopower/FlashFilerBasics.pdf on how to get the DB to point to the flat files so they could be accessed. If you don't read the instructions, the answer lies in creating an Alias to the directory containing the FF2 files.

Unfortunately the ffexplorer.exe supplied with the flashfiler download does not include an export function but does allow you to nicely see the the table structure. This is useful when you come to extract your data.

Again after some more goggling, I eventually found an ODBC driver at http://www.nexusdb.com/downloads/FlashFilerODBC213_r001.ZIP , which be used for free. Although unsupported, the driver sufficed and did the job nicely (tho not very quickly).

Once installed you need to set up a data source which points to your flash filer database. On NT systems go to the windows control panel (in classic view) then Administrative tools- Sources(ODBC). Click on add and select 'Nexus DB FlashFiler Driver'. Enter your connection details and give you data source a name.

The next problem I encountered was how to actually connect to the database over ODBC and grab the data. Without wanting to installing or write masses of Java, C# or Perl. I found the simplest way was to create some JScript that connected to the database and dumped out flat tab-separated text files. JScript is a Microsoft scripting language that has javascript like syntax. I have uploaded a generic version of the file which is located here and can be adapted by yourself. To run, simply type the fully qualified path and filename in a command window.

To get the data into Oracle you could use sqlldr or externalised tables, both use similar syntax. I used externalised tables and created a new table by doing create table as (select * from external_table).

When a you use an externalised table, the data is actually read from the text file and and cannot take advantage of the performance features Oracle uses, hence my reason for creating a table in this way. Granted, I could have bypassed this and used sqlldr or some other scripting language. The only thing to note is that you need to create a data directory mapping in the Database which will map to the location of your csv/tsv file file.

The files I have supplied may need to be adapted for your own use though. The JScript file was my quick and dirty solution to get a job done quickly and has no error checking. If it fails you'll get a javascript type alert saying so. All code supplied comes without warranty or guarantee.

Hope this saves someone a cool couple of hours or can find a use for this.

2006-12-13T14:12:00.000Z

It's been about a month since my last post and I've been busy, busy, busy. I have just got back from another trip to Prague (more on that in a later post) and my stint in Consulting Customer Services is now over and the work I did for them in APEX was very well received. Since then I've been posted to my first project outside of Oracle.

It's another project involving APEX and I'm really starting to get to grips with some of the more advanced features the product has to offer. The work I am currently involved in makes the Customer Service system I worked on seem like child's play.

For those of you not familiar with APEX, the product basically sits on top of the Oracle Database (and Oracle XE) and provides a RAD web-based development environment that allows a developer to build CRUD (Create Report Update Delete) type web applications (and more) very quickly by generating all the look and feel code itself.

APEX is ideal if your primary concern is to exploit your data and concentrate on look and feel later. Custom functionality can be added by through the use of JavaScript and PL/SQL. APEX provides dozens of wizards that provide a developer with the means to create validations, sortable reports, data-entry forms, charts and security right out of the box with very little effort.

When I first started developing in APEX, it seemed to be cumbersome and very difficult to do anything even remotely complex. By this I mean the built in wizards are fantastic but can be difficult to customize to your exact needs. Knowing how and where to change settings or add functionality also proved frustrating.

After using APEX for a couple of months now, I am fast becoming a convert as my proficiency has vastly improved and I have started to combine the out of the box features with both the underlying APEX PL/SQL API's which are not immediately obvious from the development environment.

My top tips would be to experimentation combined with quick scans of the developer documentation and forums if you start to get stuck. The APEX community is really gathering speed now and has vast amounts of information can be found there. Brushing up on your PL/SQL is the only other advice I can give as this is the quickest way to produce custom functionality.

As they say in the Czech Republic Na shledanou!

2006-11-18T14:07:00.000Z

Since everyone else is writing blogs these days and because I don’t want to feel like I am being left behind, I have finally taken the plunge and decided to start my very own.

Well partly! I suppose my main motivation comes from wanting to keep in touch with friends and uni people and extending an arm to those people I cant find the time to email.

Since this is the first entry and I originally “intended” to start blogging shortly after finishing Uni, it would only be right that the first entry contain a synopsis of my life over the last couple of months. So here it goes…

After finishing uni I worked as a bar tender in my hometown of Stafford at Après, visited Prague in the Czech Republic, moved down to Reading – the UK’s very own Silicon Valley and started my proper job with Oracle as a Technology Consultant in Applications.

I finished my technical boot-camp around 4 weeks ago and have since been doing my stint in Customer Services.

Over the last couple of days the Customer Service team has sent me all over the country. On Wednesday I found myself at the UKOUG (UK Oracle User Group Conference) in Birmingham, on Thursday I could be found somewhere in South-Wales for a management and leadership conference and on Friday I wound up back in Birmingham for the last day of the UKOUG. Lots of traveling but all good fun nonetheless.

Apart from swanning around all over the place, I’ve been working on an internal system developed in Application Express/HTMLDB and will hopefully be finishing this off over the next couple of days. Unfortunately this will have to include some of my weekend as its becoming crunch time. I’ll post an entry regarding my experiences with HTMLDB as I’ve got lots to say on it. Hopefully someone will want to here it.

So long and thanks for all the fish.