OraQA

Environment : JDK 1.5, Tomcat 5.5, Oracle 10gR2.

Let’s suppose that you have a web app where you get some records from the user interface and from your DAO, you are trying to pass the records as Oracle ARRAYS to database. Here is a step by step example.

Yes, it’s a nightmare as you have to take care of some steps. But, once you understand what to do and what are the issues, it’s pretty easy.

Use Case : Let us suppose that we have a java bean Employee and we are trying to send an array of employee records at a time to database.

/*Step 1 : Create a object type in the database*/
/*
CREATE OR REPLACE TYPE “EMP_TYPE” is object(
emp_id Varchar2(500),
emp_name varchar2(500));
*/
/*
Step 2 : Create a type EMP_TYPE_TABLE

CREATE OR REPLACE TYPE “EMP_TYPE_TAB”;
*/

/*Step 3 : Create a Java bean which maps the attributes of the above object type in Step 2.*/

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Employee implements SQLData, Serializable{
static final long serialVersionUID = 4070409649129120458L;
public Employee(){}

// constructor that takes parameters
// getters and setters for emp_id, emp_name
// You have to implement readSQL() and writeSQL() methods, as shown below.
// This is where you are mapping the Employee table’s columns to the Employee
//java bean.

public void readSQL(SQLInput stream, String typeName) throws SQLException {
this.emp_id = stream.readString();
this.emp_name = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(emp_id);
stream.writeString(emp_name);
}
}

//EmpDAO class gets a connection to the database and passes the data.

public class EmpDAO{
java.sql.Connection conn;
java.sql.Connection dconn;
/*
Step 1 : Get database connection
This is a very important step. To pass your records of data as Arrays, you need to get a oracle.jdbc.driver.T4CConnection and then use ArrayDescriptor’s. So, how do you get a T4CConnection ?

To get T4CConnection from java.sql.Connection, you need to cast like this :
t4cConn = ((DelegatingConnection)conn).getInnermostDelegate();

If you are working on tomcat, you have two options to get a DataSource in your context.xml.
a) By using apache commons-dbcp
OR
b) by directly using javax.sql.DataSource.
Let’s see how to get the T4CConnection in both these cases.
*/

public void sendRecordsToDB(){

//Use Case (a) : if you configured apache commons-dbcp
BasicDataSource ds = (BasicDataSource)ctx.lookup(jndiName);
ds.setAccessToUnderlyingConnectionAllowed(true);
conn = ds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

//Use Case (b) : if you are directly using javax.sql.DataSource

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName(”");
bds.setUsername(”");
bds.setPassword(”
“);
bds.setUrl(”jdbc:oracle:thin:@”);
bds.setAccessToUnderlyingConnectionAllowed(true);
conn = bds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

/*So, using either of the above approaches we got dconn, which is an instance of T4CConnection.*/

/* Now let’s build an array list of employees.
*/
final List listOfEmployees = new LinkedList();

Employee e1 = new Employee();
e.setEmpId(1);
e.setEmpName(”Ravi”);

listOfEmployees.add(e1);

Employee e2 = new Employee();
e.setEmpId(2);
e.setEmpName(”Vedala”);

listOfEmployees.add(e2);

// Now, create an array descriptor

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( “EMP_TYPE_TAB”, dconn );

ARRAY array_to_pass = new ARRAY( descriptor, dconn, (Object[]) listOfEmployees.toArray());
ps = (OracleCallableStatement)dconn.prepareCall(”begin insert_employees(:1); end;”);
ps.setARRAY( 1, array_to_pass );
ps.execute();
conn.commit();
}

/*
- See how simple and beautiful is the procedure.
- Using the TABLE() function, you can treat the whole array as a table as EMP_TYPE_TAB is a nested table.
*/

PROCEDURE insert_employees(p_emparray in EMP_TYPE_TAB) AS
BEGIN
/* INSERT ARRAY OF RECORDS IN TO THE EMP TABLE*/
INSERT INTO scd_company_staging
(emp_id,emp_name)
SELECT * FROM TABLE(p_comparray);
END insert_employees;

The nightmare exception for Java/Oracle developers :-)

java.lang.ClassCastException: oracle.jdbc.driver.T4CConnection cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:149)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)
………..

Solution :
a)You will see the above exception, if you have ojdbc14.jar in your war file. You would be having ojdbc14.jar on your classpath for compiling your java classes. Use it only for compilation. Don’t include it in the build to Tomcat. ie., the war file of your web app should NOT have ojdbc14.jar in it.

b) Make sure that the Oracle thin driver (eg : ojdbc14.jar) is in tomcat’s common\lib.

Long live “Tom Kyte”.

Good Luck !!
r-a-v-i

Filed under Jdeveloper, SQL. Follow any responses to this entry through this RSS 2.0 feed.