I want to cast a sql types structure into a java object.
Hi ,
I have one query,have one class like Emp,and using orcale types for my code.
I am retreiveing the data from a pl/sql function through a colection.
Then i assinging these chunk of data into a sql types Struct.Now I want cast these STRUCT into a object of Emp class.
I have given the source in below.
Can anyone help me how can I do that.
create or replace
type emp_rec as object (empno number(4), ename varchar2(20), sal number(3));
create or replace
type emp_tab as table of emp_rec;
create or replace
function sendemp return emp_tab
as
emp_table emp_tab :=emp_tab();
begin
for i in (select * from emp)
loop
emp_table.extend;
emp_table(emp_table.last):=emp_rec(i.empno,i.ename,i.sal);
end loop;
return emp_table;
end;
package com.jtest;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
class Emp
{
private int empno;
private String ename;
private int sal;
public void setEmpno(int empno)
{
this.empno=empno;
}
public void setEname(String ename)
{
this.ename=ename;
}
public void setSal(int sal)
{
this.sal=sal;
}
public int getEmpno()
{
return(empno);
}
public String getEname()
{
return(ename);
}
public int getSal()
{
return(sal);
}
}
public class CallFunction
{
public static void main(String args[])
{
String driver = "oracle.jdbc.driver.OracleDriver";
String cs = "jdbc:oracle:thin:@123.100.100.1:1521:orcl";
String uname = "scott";
String pword = "tiger";
try
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(cs, uname, pword);
int no=0;
oracle.jdbc.driver.OracleCallableStatement ocs = (oracle.jdbc.driver.OracleCallableStatement)conn.prepareCall("{? = call sendemp()}");
ocs.registerOutParameter(1,OracleTypes.ARRAY,"EMP_TAB");
//ocs.registerOutParameter(1, Types.JAVA_OBJECT, "EMP_TAB");
ocs.execute();
Array array = (ARRAY) ((OracleCallableStatement)ocs).getOracleObject(1);
ResultSet rs=array.getResultSet();
while(rs.next())
{
STRUCT obj= (STRUCT)rs.getObject(2);
Object attrs[]=obj.getAttributes();
String s1=attrs[0].toString();
String s2=attrs[1].toString();
String s3=attrs[2].toString();
System.out.println(s1+" "s2" "+s3);
}
//Emp e=(Emp)((OracleCallableStatement)ocs).getOracleObject(1)); --I want to do this,is it possible.
}
catch(Exception e1)
{
e1.printStackTrace();
}
}
}
Thanks