Java Applications Assessment - Oracle to SQL Server Migration

If you have Java programs that worked with an Oracle database, they may require changes after you have migrated the database to Microsoft SQL Server or SQL Azure.

Java application assessment helps you to identify the required changes, the conversion cost and time.

• Is it required to support both Oracle and SQL Server databases in the application?

What development and runtime environments are used for Java applications?

• Eclipse, NetBeans, JDeveloper, IntelliJ IDEA i.e.
• WebLogic, JBoss, Apache Tomcat i.e.
• Standalone Java application

What the JDBC driver and connection options are used?

• JDBC Driver - oracle.jdbc.driver.OracleDriver
• Driver Type - thin, OCI

How the database access is performed?

• Prepared statemens or SQL string concatenations to define parameters
• Stored procedure calls
• Metadata access
• DDL statements (CREATE TABLE, VIEW, INDEX, SEQUENCE etc.)

Analyzing JDBC interfaces, classes and methods help you understand how the application interacts with the database - DML operations, procedure calls, metadata access etc.

1. java.sql.Connection
2. java.sql.CallableStatement
3. java.sql.DriverManager
4. java.sql.PreparedStatement
5. java.sql.ResultSet
6. java.sql.ResultSetMetaData
7. java.sql.SQLException
8. java.sql.Types

After you have analyzed JDBC methods, you can review Oracle specific syntax that is used in SQL statements and requires changes for SQL Server. Typically these are:

• Outer joins (+)
• || String concatenations
• Built-in functions - DECODE, NVL, TO_DATE, TO_NUMBER, TO_CHAR etc.
• CREATE TABLE statements with Oracle data types such as VARCHAR2, RAW, NUMBER, TIMESTAMP, BLOB etc.
• Reference to Oracle catalog views

• Are there any external files (SQL scripts, XML files etc.) containing SQL statements that are used by the Java application?
• Is there any specific format is used to represent SQL, or plain SQL is stored?

These statements also may contain Oracle specific syntax and require analysis.