Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

I want to cast a sql types structure into a java object.

user11415386Jun 15 2012 — edited Jun 18 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 16 2012
Added on Jun 15 2012
4 comments
2,174 views