First steps into the Oracle Database Cloud

Oracle provides a Database Cloud Service. In a previous post I've looked at the Oracle Java Cloud Service (http://javaoraclesoa.blogspot.nl/2013/12/first-steps-into-oracle-java-cloud.html). The database is of course also an important component used in most applications. In this blog post I'll describe my first experiences with the Oracle Database Cloud service. I've used two methods to connect to the Oracle Database Cloud service. The first one from SQLDeveloper. Next I created a webservice, deployed it to the Oracle Java Cloud service and fetched data from the Oracle Database Cloud service with it.

There are of course other methods to interact with the Oracle Database Cloud service. It is for example possible using SQLWorkshop from the Apex interface to expose RESTful services to access the database. See for example http://multikoop.blogspot.nl/2012/11/oracle-java-and-database-cloud-services.html on how to create these services. By default, calls to the Oracle Cloud are encrypted. See https://weblogs.java.net/blog/bleonard/archive/2013/05/02/calling-oracle-cloud-service-java on how to call services.

Connections

SQLDeveloper

For SQLDeveloper I used the following documentation to get started; http://docs.oracle.com/cloud/CSDBU/develop.htm

First make SFTP available;
- Go to the Services page. In my case https://myservices.em1.cloud.oracle.com/mycloud/f?p=my_services (See Welcome to the Oracle Cloud mail)
- Write down the SFTP user

- Go to the identity console

- Change the password of the SFTP user

- Use the settings in SQLDeveloper and connect!

Java Webservice

The following post provides some useful information on accessing the Oracle Database Cloud from the Oracle Java Cloud; https://weblogs.java.net/blog/bleonard/archive/2013/01/23/moving-premise-applications-oracle-cloud. If the clouds are associated, you can use the name of the database as JNDI name for a datasource. For a simple example of JDBC with the Oracle driver look at; http://www.mkyong.com/jdbc/connect-to-oracle-db-via-jdbc-driver-java/

So first I tried the following (simple JAX-WS webservice);

   public String getTime() throws SQLException {
        String query = "select sysdate from dual";
        Connection con = DriverManager.getConnection(
"database", "triala65",
"notmypassword");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        String result = "";
        while (rs.next()) {
            result = rs.getString(1);
        }
        return result;
    }

Upon deployment I discovered the java.sql.DriverManager was not whitelisted...

2013-12-12 06:07:09 CST: ERROR    - There are 1 error(s) found for cloudserviceX1X0XSNAPSHOT.war
2013-12-12 06:07:09 CST: ERROR    - Path:cloudserviceX1X0XSNAPSHOT.war (1 Error)
2013-12-12 06:07:09 CST: ERROR     - Path:cloudserviceX1X0XSNAPSHOT.war (1 Error)
2013-12-12 06:07:09 CST: ERROR      - Class:ms.test.cloud.testcloudservice (1 Error)
2013-12-12 06:07:09 CST: ERROR       - 1:Type "java.sql.DriverManager" not allowed.

In this case I was trying to obtain a direct JDBC connection without using the JNDI name. I refactored my code to use the JNDI name;

   public String getTime() {
        String result = "";
        try {
            String query = "select sysdate from dual";
            Context context = new InitialContext();
            DataSource dataSource = (javax.sql.DataSource) context.lookup("database");
            Connection con = dataSource.getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            
            while (rs.next()) {
                result = rs.getString(1);
            }
            
        } catch (NamingException ex) {
            result = "NamingException: "+ex.getMessage()+" explanation: "+ex.getExplanation();
            Logger.getLogger(testcloudservice.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            result = "SQLException: "+ex.getMessage()+" SQLState: "+ex.getSQLState();
            Logger.getLogger(testcloudservice.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

When I called the service from SOAP-UI, It fetched the date from the database;

Conclusion

Easy

It is relatively easy to access the Oracle Database Cloud from the Oracle Java Cloud. Also it is relatively easy to configure a connection to the Oracle Database Cloud from a local SQLDeveloper. The Oracle Database Cloud provides facilities to batch upload/download large amounts of data (not tested in this post but described in the referred documentation) and can be accessed/administered using Oracle APEX. Functionality can also be exposed as RESTful services by using this interface.

Limitations

You are not allowed to access external databases directly from the Oracle Java Cloud; java.sql.DriverManager which is required for building a connection, is not whitelisted. Also I have not seen a way to directly create a datasource in the Oracle Java Cloud service. Based on http://docs.oracle.com/cloud/CSJSU/dev_app.htm#BCEIAIBI: 'a single XA enabled JDBC data source' my estimate is that the autogenerated datasource is XA enabled. When migrating to the Cloud, this might require changes to the database code; it should not contain savepoints, commits and the like.