Getting the Array of Objects from Oracle stored procedure through Java code

Hi All,

I had a requirement, like, get Array of Objects from Oracle stored procedure by using Java (JDBC) code. Means, my Oracle stored procedure will return Array type of output variable. That array type will hold the Objects(which is user defined objects).This Array type of variable need to get it by JDBC code. So for this I did the coding in the following manner.

I guess this may be helpful to the guys who is having the same type of requirement( get the Array of Objects or only Object from the DataBase).

From DB side :-

1. I have crated the Object(person) as follows:

create or replace type OBJ_PERSON_INFO as object (
p_name varchar2(30),
p_age number,
p_mobile_no number
)

2. I have created the Array of Object variable as follows:

create or replace TYPE ARR_PERSON_INFO AS VARRAY(50) OF OBJ_PERSON_INFO;

3. I have created the stored procedure as follows(with hard coded values):

create or replace procedure get_persons_info
(
i_area_code in varchar2, /* input parameter */
o_arr_persons out arr_person_info, /* output parameters */
o_status_code out varchar2,
o_status_msg out varchar2
) AS
ProcedureName varchar2(30) := 'get_persons_info';
begin
/**************************************** Main Function ***********************************************/

o_arr_persons := arr_person_info(obj_person_info('Ramu',25,9916379951),obj_person_info('Siva',26,9247469543),obj_person_info('Saran',30,99868583));

o_status_code := '0'; -- Success
o_status_msg := 'Success';

/**************************************** Error Handler ***********************************************/
exception
when others then
o_status_code := '-100';
o_status_msg := sqlcode || substr (sqlerrm, 1, 500) || ' in the SP:' || ProcedureName;

end get_persons_info;

/* End of stored procedure */


The above stored procedure will return 3 output paramertes. Those are :
1. Array Of Person Objects
2. Status Code
and 3. Status Message.

Here, the "o_arr_persons" object will hold the 3 persons details(We hard coded with 3 records).

From Java (JDBC) side :

I have created a java class to get the details of persons from the Oracle DB by using JDBC (Callable statement).

The code of this calss is :

//Code starts here

package com.ramu.dbutil.services;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
* @author : Ramu.Polamreddy
* Created Date : 08/JUN/2009
* Modified Date :
* Modified By :
* Description : Get the array of person details from Oracle DB stored procedure
* and display those records on the console.
*/

public class GetPersonDetails {

//Variable declarations

private Connection connection = null;
private CallableStatement callableStatement = null;
private ResultSet resultSet = null;

//End of variable declarations

//Return the Connection object
public Connection getConnection()throws ClassNotFoundException, SQLException, Exception
{
System.out.println("Entered into GetPersonDetails.getConnection method");

//DB Properties
String driverName = "oracle.jdbc.OracleDriver"; //Driver name
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe"; //JDBC URL
String username = "person"; //User Name
String password = "person"; //Password

//Load the JDBC driver and get connection
Class.forName(driverName);
connection = DriverManager.getConnection(url, username, password);

System.out.println("Returning the Connection object from GetPersonDetails.getConnection method");

return connection;
}

public void getArrPersonObjects() {


System.out.println("Entered into GetPersonDetails.getArrPersonObjects method");

try
{
//Getting Connection object
connection = getConnection() ;


//Get Docket number from Back end
callableStatement = connection.prepareCall("call get_persons_info(?,?,?,?)");

//Set Input parameters
callableStatement.setString (1, "NLR1"); //I_AREA_CODE

System.out.println("Input param is registered...");
//Register output parameters
callableStatement.registerOutParameter (2, Types.ARRAY, "ARR_PERSON_INFO"/* Name of the OBJECT/ARRAY, Which we have created at Oracle DB */); //O_ARR_PERSONS
System.out.println("Output param ARRAY is registered...");
callableStatement.registerOutParameter (3, Types.VARCHAR); //O_STATUS_CODE
callableStatement.registerOutParameter (4, Types.VARCHAR); //O_STATUS_MSG
System.out.println("Output params are registered...");

// Call the stored procedure
callableStatement.executeUpdate();

System.out.println("executeUpdate() is done");

/*

//Suppose in case if we want to get only one object
//i.e., If Our stored procedure is returning only Object type
//(Person and we need to mention the name of the Object while
//registering the parameter, like above)[not Array of objects]
//then the following code will help to get that

oracle.sql.STRUCT person = (oracle.sql.STRUCT) callableStatement.getObject(1);
Object[] personValues = person.getAttributes();
String pName = (String) personValues[0];
String pAge = (String) personValues[1];
String pMbNo = (String) personValues[2];

*/


System.out.println("******************************");

// Get the output parameter values
oracle.sql.ARRAY objArray = (oracle.sql.ARRAY) callableStatement.getObject(2);

System.out.println("oracle.sql.ARRAY created");

resultSet = objArray.getResultSet();

System.out.println("Got the Result set object from oracle.sql.ARRAY");

if (resultSet != null && resultSet.getFetchSize() > 0)
{
System.out.println("ResultSet is not empty");
System.out.println("Got the size of the resultSet as : " + resultSet.getFetchSize());
}
else
{
System.out.println("ResultSet is empty");
}
while (resultSet.next())
{
//Display the record number on console
System.out.println("Got Record : " + resultSet.getRow() + " from result set");
// The first column contains the element index and the
// second column contains the element value
System.out.println(">> index " + resultSet.getInt(1)+" = " + resultSet.getObject(2));

//We can get the Oracle Object type data by oracle.sql.STRUCT object
oracle.sql.STRUCT myStruct = (oracle.sql.STRUCT) resultSet.getObject(2);

//Getting the attributes/properties of Oracle Object type as array of java Objects
Object[] personInfo = myStruct.getAttributes();

//Display all the details on the console
System.out.println("-----------------");
System.out.println("Person Name : " + personInfo[0]);
System.out.println("Person Age : " + personInfo[1]);
System.out.println("Mobile Number : " + personInfo[2]);
System.out.println("------------------");
}


System.out.println("********************************");

//Get and log the DB Stored Procedure returned status values
String statusCode = callableStatement.getString(3);
String statusMsg = callableStatement.getString(4);

System.out.println("Got the DB stored procedure Status details as...");
System.out.println("Status Code : " + statusCode);
System.out.println("Status Msg : " + statusMsg);

}
catch (Exception exception) {

System.err.println("Got Exception as : " + exception.getMessage());
exception.printStackTrace();
}

finally
{

try
{
if (resultSet != null)
{
resultSet.close();
System.out.println("Result Set is closed");
}

if (callableStatement != null)
{
callableStatement.close();
System.out.println("Callable Statement is closed");
}

if (connection != null)
{
connection.close();
System.out.println("Connection closed");
}

}
catch (Exception exception) {

System.err.println("Got Exception : " + exception.getMessage());
}
}

System.out.println("Exiting from GetPersonDetails.getArrPersonObjects() method");

}

public static void main(String[] args) {

System.out.println("Testing this Sample code");
System.out.println("Calling the GetPersonDetails.getArrPersonObjects() method");
GetPersonDetails getPersonDetails = new GetPersonDetails();
getPersonDetails.getArrPersonObjects();

System.out.println("Done");
}


}

//End of the java code

Thanks and Regards,
Ramu.