Tag Archives: jdbc

Oracle JDBC Connection – Moving from Oracle Single Instance to Oracle RAC Grid

Our core oracle database has been moved to oracle single instance to Oracle RAC Grid.

I had some old Java systems, which were using JDBC connection to connect Oracle. When system moved to RAC, my systems wasn’t working. Now, I need to change my OS host file and JDBC connection string. FYI,  172.17.1.236 is my Oracle RAC’s scan ip.

For Windows,

go to C:\Windows\System32\Drivers\etc\hosts and the following entry

172.17.1.236 ORACLE-RAC

for Linux,

go to /etc/hosts and add the following entry

172.17.1.236 ORACLE-RAC

Done for OS part. Now the connection String.

Read more of this post

Connect to Oracle from Java via JDBC for calling Stored Procedure

My application runs on postgre. I had spring-hibernate connection from my JSF to Postgre. But, for login I needed to connect to the mother application. My core application runs on oracle. User authentication can be found through a stored procedure. I couldn’t find anything, so that I connect to databases at a time. So, took the help of JDBC. I needed the library jar for the connection to oracle. I took the ojdbc14.jar. In the stored procedure there were 5 parameters. First 2 of the are in parameter and last 2 are out parameter including one cursor. 2 IN parameter will carry the username and password and will bring back user authentication. 3rd parameter will indicate whether it is valid user (0 for valid user), 4th parameter will carry error message(if any). And if it is valid user 4th parameter will carry user related information.

String driver = “oracle.jdbc.driver.OracleDriver”;
String url = “jdbc:oracle:thin:@127.0.0.1:1521:mydatabase”;
String dbuser = “oracle”;
String dbpass = “oracle”;

Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, dbuser, dbpass);

String proc3StoredProcedure = “{ call usr_login_info(?, ?, ?, ?, ?) }”;
CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
cs.setString(1, userName);
cs.setString(2, password);
cs.registerOutParameter(3, java.sql.Types.DOUBLE);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
cs.registerOutParameter(5, OracleTypes.CURSOR);
cs.execute();
Double param3 = cs.getDouble(3);
String errorMessage = cs.getString(4);

User user=new User();

if (param3.equals(new Double(“0.0”)))

{

      ResultSet rs = (ResultSet) cs.getObject(5);
      while (rs.next())
      {
            user.setUserId(rs.getString(1));
            user.setDeptId(rs.getString(2));
            user.setUserName(rs.getString(3));
            user.setAddress(rs.getString(4));
      }

}

else
{
      System.out.println(“failed”);
      user.setLoginMessage(errorMessage);
}

conn.close();