================= This For loop in MAIN class which will create 30 session =========================
import java.io.*;
import oracle.jdbc.pool.OracleOCIConnectionPool;
import java.util.Properties;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalTime;
public class InsertMain {
public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
if (args.length < 1) {
System.out.println("Usage: Java LOBTest <parameter file>");
return;
}
try {
Parameters.parseFile(args[0]);
} catch (IOException e) {
System.out.println("Error parsing parameter file " + args[0]);
return;
}
Connection[] conn = new Connection[Parameters.p_sessions];
System.out.print("Register driver ... ");
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
} catch (SQLException e) {
System.out.println("Failed in registering Driver.");
return;
}
System.out.println("Succeed in registering the Driver.");
try {
Properties poolConfig = new Properties();
poolConfig.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "70");
poolConfig.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "400");
poolConfig.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2");
OracleOCIConnectionPool cpool = new OracleOCIConnectionPool(Parameters.p_username, Parameters.p_password,
Parameters.p_connect_string, poolConfig);
System.out.println("START Date-Time :" + LocalDate.now() + " " + LocalTime.now());
for (int i = 0; i < 30; i++) {
System.out.println("inside for loop");
conn[i] = cpool.getConnection(Parameters.p_username, Parameters.p_password);
ThreadPLSQL thread = new ThreadPLSQL(Parameters.p_imgtest02_destable, conn[i]);
thread.start();
System.out.println("inside for loop after start");
try { // starting workload thread 10 seconds=10000
Thread.currentThread().sleep(200);
} catch (InterruptedException e) {
}
}
System.out.println("All Threads generated");
} catch (Exception ex) {
System.out.println("Connection String failed");
System.out.println("Error: " + ex);
ex.printStackTrace();
return;
} finally {
System.out.println("Closing all connections");
for (int i = 0; i < Parameters.p_sessions; ++i)
if (conn[i] != null)
conn[i].close ();
}
}
}
======================= Class ThreadPLSQL ==================================
//p_connect_string = jdbc:oracle:oci:@testdb
/* ThreadClassInsert:
* */
import java.util.*;
import java.io.*;
import java.sql.*;
public class ThreadPLSQL extends Thread {
int p_number;
String nm = "test";
String tName;
String[] tId;
int p_basetablesize;
Random rand = new Random();
Connection connThread;
public ThreadPLSQL(String basetablename, Connection conn) {
p_number = Parameters.p_imgtest01_end;
connThread = conn;
}
public void run() {
long t_start = System.currentTimeMillis();
long t_now = t_start;
try {
CallableStatement cs = connThread.prepareCall("{? = call Thunder.Fn_Schemacreation(?,?)}");
// while (t_now - t_start < Parameters.p_duration * 10) {
tName = Thread.currentThread().getName();
tId = tName.split("-", 0);
System.out.println("Running thread number:"+tId[1]);
try {
// do_plsql (tId[1], connThread);
cs.registerOutParameter(1, Types.VARCHAR); // The first ?
cs.setString(2, tId[1]);
cs.setInt(3, 100000);
cs.execute();
String rvalue = cs.getNString(1);
System.out.println("The retrun value for Thread No: " + tId[1] + " is:" + rvalue);
cs.close();
connThread.close ();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
try {
Thread.currentThread().sleep(Parameters.p_thinktime);
} catch (InterruptedException e) {
}
t_now = System.currentTimeMillis();
// } // while loop ends
} catch (SQLException e) {}
}
}
The above code should open 30 connections to DB and each connection will call PLSQL package Thunder.Fn_Schemacreation. But when I am executing the code only 20-25 connections get opened and rest are throwing below exception. Shall I need to increase the connection pool or some other parameter which I am missing?
java.sql.SQLException: ORA-01109: database not open
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1256)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1144)
at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:755)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1032)
at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:84)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:681)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:602)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at DBConnection.open(DBConnection.java:67)
at DBConnection.conn(DBConnection.java:44)
at ThreadPLSQL.run(ThreadPLSQL.java:35)
Failed.
Exception in thread "Thread-27" java.lang.NullPointerException
at ThreadPLSQL.run(ThreadPLSQL.java:35)