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 :

execute DBMS_SNAPSHOT.REFRESH('VIEWNAME','c');

Where VIEWNAME is the view you want to refresh.

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

http://www.databasejournal.com/features/oracle/article.php/2200191/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm

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')");
stmt.execute();



For sql developer you can just do :
call DBMS_SNAPSHOT.REFRESH('VIEWNAME','c');


It should work like a charm :)

Enjoy!