Passing Nested Type from Java to DB

[arpit kumar Dubey]

I have following 2 types

CREATE OR REPLACE TYPE "GRSO_COMPETENCY" as object
(
COMPETENCY_ID NUMBER(5),
COMPETENCY_DESC VARCHAR2(250),
PROFICIENCY_ID NUMBER(3)
);

CREATE OR REPLACE TYPE grso_competency_array AS TABLE OF grso_competency;


CREATE OR REPLACE TYPE "MASTER_TYPE" as object
(
CANDIDATE_ID number,
COMPETENCY_DTLS grso_competency_array
);

CREATE OR REPLACE TYPE MASTER_TYPE_ARRAY AS TABLE
OF MASTER_TYPE
/


Now i have made 2 VO

1st VO - THis is Child VO


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

public class ChildArrayVO implements Serializable,SQLData {

/**
*
*/
private String sql_type;
private Integer CompetencyID =null;
private String CompetencyDesc=null;
private Integer ProfId=null;
public ChildArrayVO() {
}

public ChildArrayVO(String sql_type,Integer CompetencyId,String CompetencyDesc,Integer ProfId) {
this.sql_type=sql_type;
this.CompetencyID=CompetencyId;
this.CompetencyDesc=CompetencyDesc;
this.ProfId=ProfId;
}

public String getSQLTypeName() throws SQLException
{
return sql_type;
}

public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
this.CompetencyID=stream.readInt();
this.CompetencyDesc=stream.readString();
this.ProfId=stream.readInt();
}

public void writeSQL(SQLOutput stream) throws SQLException {

stream.writeInt(CompetencyID);
stream.writeString(CompetencyDesc);
stream.writeInt(ProfId);

}

public String getCompetencyDesc() {
return CompetencyDesc;
}

public void setCompetencyDesc(String competencyDesc) {
CompetencyDesc = competencyDesc;
}

public Integer getCompetencyID() {
return CompetencyID;
}

public void setCompetencyID(Integer competencyID) {
CompetencyID = competencyID;
}

public Integer getProfId() {
return ProfId;
}

public void setProfId(Integer profId) {
ProfId = profId;
}


}


2nd VO - THis is Parent VO


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

public class ArrayVO implements Serializable,SQLData {

/**
*
*/
private String sql_type;
private Integer CandidateId =null;
private ChildArrayVO childRecord=null;

public ArrayVO() {
}

public ArrayVO(String sql_type,Integer CandidateId,ChildArrayVO childRecord) {
this.sql_type=sql_type;
this.CandidateId=CandidateId;
this.childRecord=childRecord;

}

public String getSQLTypeName() throws SQLException
{
return sql_type;
}

public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
CandidateId = stream.readInt();
//childRecord.setCompetencyID(stream.readInt());
//childRecord.setCompetencyDesc(stream.readString());
//childRecord.setProfId(stream.readInt());
childRecord = (ChildArrayVO) stream.readObject();
}

public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(CandidateId);
//stream.writeInt(childRecord.getCompetencyID());
//stream.writeString(childRecord.getCompetencyDesc());
//stream.writeInt(childRecord.getProfId());

stream.writeObject(childRecord);

}

public Integer getCandidateId() {
return CandidateId;
}

public void setCandidateId(Integer candidateId) {
CandidateId = candidateId;
}

public ChildArrayVO getChildRecord() {
return childRecord;
}

public void setChildRecord(ChildArrayVO childRecord) {
this.childRecord = childRecord;
}


}


I want to pass the Nested Type Array

The Main code is as below
-----------------------------

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.util.Dictionary;


import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;


public class NestedArraytoDB {

public NestedArraytoDB() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@172.90.12.85:1521/TEST";
java.sql.Connection con = DriverManager.getConnection(url,"scott", "tiger123");

System.out.println("hello");

Dictionary map1 = (Dictionary)con.getTypeMap();
Dictionary map2 = (Dictionary)con.getTypeMap();
Thread thread = Thread.currentThread();
ClassLoader classLoader = thread.getContextClassLoader();
//Corresponding mapping java class to be used.
Class classComp1 = classLoader.loadClass("ArrayVO");
Class classComp2 = classLoader.loadClass("ChildArrayVO");

map1.put("MASTER_TYPE", classComp1);
map2.put("GRSO_COMPETENCY", classComp2);

ArrayDescriptor arrayDesc1 = ArrayDescriptor.createDescriptor( "MASTER_TYPE_ARRAY", con);
ArrayDescriptor arrayDesc2 = ArrayDescriptor.createDescriptor( "GRSO_COMPETENCY_ARRAY", con);


ArrayVO[] arrayList;
ChildArrayVO[] childList;

ChildArrayVO childArrayVO = new ChildArrayVO("GRSO_COMPETENCY",
11,
"Test12",
11

);

ArrayVO arrayVO = new ArrayVO("MASTER_TYPE",
1,
childArrayVO
);

arrayList = new ArrayVO[1];

arrayList[0]=arrayVO;

//childList=new ChildArrayVO[1];
//childList[0]=childArrayVO;


ARRAY arrComp = new ARRAY(arrayDesc1, con, arrayList);

System.out.println(arrayVO.getCandidateId());
System.out.println(arrayVO.getChildRecord().getCompetencyDesc());

}



public static void main(String[] args){
try{
new NestedArraytoDB();
}catch(Exception e ){e.printStackTrace();}


}
}


Can any one help please.....