Monday, May 11, 2009

Oracle Materialized View Refresh using JDBC

I am a big fan of Materialized views. When used effectively it somewhat decreases the requirement to use Oracle Analysis (if all you want is queries and not pivots).

You can refresh a materialized view using the following SQL Script :


Where VIEWNAME is the view you want to refresh.

For a detailed note one the parameters take a look at :

However this is not a valid sql statement.

Using Oracle SQL developer you can execute it using the "Run script" button as shown:

However using the Execute script button with give you an error (Invalid SQL Statement):
The same error is observed if you were to do the following with jdbc:
stmt = connection.createStatement();
stmt.executeQuery("execute DBMS_SNAPSHOT.REFRESH('VIEWNAME','c')") ;

This is because statements cannot contain calls to stored procedures :). Don't know why, but that's the way it is.

Instead you should do the following:
stmt = connection.prepareCall("call DBMS_SNAPSHOT.REFRESH('VIEWNAME','c')");

For sql developer you can just do :

It should work like a charm :)