Some times it’s necessary that we get the data from the Oracle PL/SQL procedures as TABLE type objects. Retrieving the data from the table type objects is not the same as we retrieve the data from ResultSet. So here is a step wise procedure with complete example for the benefit of PL/SQL and Java developers.

1. Create a table EMPLOYEE
CREATE TABLE EMPLOYEE_MASTER
( EMPID NUMBER(10,0) PRIMARY KEY,
FNAME VARCHAR2(20 BYTE),
LNAME VARCHAR2(20 BYTE),
DOB_DATE DATE,
SALARY NUMBER(10,0),
REMARKS VARCHAR2(50 BYTE)
) ;

2. Create an object and its body for new object creation. Body is useful for testing the procedure with in the PLSQL code.

CREATE OR REPLACE TYPE EMP_RECORD_OBJ AS OBJECT(
FIRSTNAME varchar2(20),
LASTNAME varchar2(20),
SALARY number(10),
DOB DATE,
REMARKS varchar2(50),
STATIC FUNCTION GET_OBJ_FUNCTION RETURN EMP_RECORD_OBJ
);

-// EMP_RECORD_OBJ body creation helpful for creating a new object with null values
CREATE OR REPLACE TYPE BODY EMP_RECORD_OBJ
IS
STATIC FUNCTION GET_OBJ_FUNCTION RETURN EMP_RECORD_OBJ
IS
BEGIN
RETURN EMP_RECORD_OBJ (NULL, NULL, NULL, NULL, NULL);
END;
END;

3. Create a table type (create under a schema directly to call from jdbc but not inside the package.)

CREATE OR REPLACE TYPE EMP_RECORD_OBJ_ARRAY IS TABLE OF EMP_RECORD_OBJ ;

4. Create a procedure to return the table type object from the table

CREATE OR REPLACE PROCEDURE GET_TABLE_OUTPUT (
START_EMPID IN NUMBER,
END_EMPID IN NUMBER,
RECORDS_LIST OUT EMP_RECORD_OBJ_ARRAY )
AS
v_emp_array EMP_RECORD_OBJ_ARRAY;
emp_rec EMPLOYEE_MASTER%rowType;
rec_index NUMBER;
CURSOR EMPLOYEE_CUR IS SELECT EMPID , FNAME, LNAME, DOB_DATE, SALARY,
REMARKS FROM EMPLOYEE_MASTER;
BEGIN
IF v_emp_array.EXISTS(1) THEN v_emp_array.DELETE(); END IF;
v_emp_array := EMP_RECORD_OBJ_ARRAY();
rec_index := 0;

OPEN EMPLOYEE_CUR;
LOOP
FETCH EMPLOYEE_CUR INTO emp_rec;
EXIT WHEN EMPLOYEE_CUR%NOTFOUND;
IF emp_rec.empid >= start_empid and emp_rec.empid <= end_empid THEN v_emp_array.EXTEND(); rec_index := rec_index+1; v_emp_array(rec_index) := EMP_RECORD_OBJ.GET_OBJ_FUNCTION(); v_emp_array(rec_index).FIRSTNAME := emp_rec.fname; v_emp_array(rec_index).LASTNAME := emp_rec.lname; v_emp_array(rec_index).SALARY := emp_rec.salary; v_emp_array(rec_index).DOB := emp_rec.dob_date; v_emp_array(rec_index).REMARKS := emp_rec.remarks; -- DBMS_OUTPUT.PUT_LINE(emp_rec.fname || 'is -->> ‘ || emp_rec.remarks);
END IF;
END LOOP;
CLOSE EMPLOYEE_CUR;
RECORDS_LIST := v_emp_array;
EXCEPTION WHEN OTHERS THEN
RECORDS_LIST := NULL;
END;

5. Now enter some master records for testing
INSERT INTO EMPLOYEE_MASTER VALUES (1, ‘KHALEEL’,’SHAIK’, SYSDATE-9000, 10000, ‘PRACTICE HEAD’);
INSERT INTO EMPLOYEE_MASTER VALUES (2, ‘RAJA’,’RAO’, SYSDATE-8000, 99000, ‘MANAGER’);
INSERT INTO EMPLOYEE_MASTER VALUES (3, ‘RANGA’,’RAJU’, SYSDATE-9200, 9000, ‘LEAD’);
INSERT INTO EMPLOYEE_MASTER VALUES (4, ‘RAMANA’,’REDDY’, SYSDATE-9100, 11000, ‘MANAGER PROJECTS’);
INSERT INTO EMPLOYEE_MASTER VALUES (5, ‘MOHAN’,’RAO’, SYSDATE-9100, 20000, ‘SOLARIS ADMIN’);
INSERT INTO EMPLOYEE_MASTER VALUES (6, ‘PRANEETH’,’M’, SYSDATE-6000, 8000, ‘PROGRAMMER’);
INSERT INTO EMPLOYEE_MASTER VALUES (7, ‘SHIVA’,’REDDY’, SYSDATE-8500, 11000, ‘PROGRAMMER’);
INSERT INTO EMPLOYEE_MASTER VALUES (8, ‘SURENDER’,’KUMAR’, SYSDATE-9000, 10000, ‘LEAD PROGRAMMER’);
INSERT INTO EMPLOYEE_MASTER VALUES (9, ‘MUNNA’,’BHAI’, SYSDATE-10000, 20000, ‘BHAI’);
INSERT INTO EMPLOYEE_MASTER VALUES (10, ‘RANI’,’RARU’, SYSDATE-3000, 10000, ‘HR’);
COMMIT;

6. Testing the procedure from PL/SQL

SET serveroutput on;

DECLARE
TEST_emp_array EMP_RECORD_OBJ_ARRAY;
BEGIN
GET_TABLE_OUTPUT (1, 4, TEST_emp_array);
FOR index IN 1 .. TEST_emp_array.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(‘Here is a last name: ‘|| TEST_emp_array (index).LASTNAME);
END LOOP;
END;

Note: Now you should be able to see the below output:
Here is a last name: SHAIK
Here is a last name: RAO
Here is a last name: RAJU
Here is a last name: REDDY

7. Testing the procedure from java through JDBC call.

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

import oracle.sql.StructDescriptor;

public class TestOracleTableOfResult {

public static void main(String…a) throws Exception {
Class.forName(“oracle.jdbc.OracleDriver”);
Connection connection = DriverManager.getConnection(“jdbc:oracle:thin:scott/tiger@localhost:1521:XE”);

final String typeName = “EMP_RECORD_OBJ”;
final String typeTableName = “EMP_RECORD_OBJ_ARRAY”;
SimpleDateFormat df = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss.S”);

// Get a description of your type (Oracle specific)
final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);
final ResultSetMetaData metaData = structDescriptor.getMetaData();

// Call the procedure (or whatever else) that returns the table of a custom type
CallableStatement cs = connection.prepareCall(“{call GET_TABLE_OUTPUT(?, ?, ?)}”);
cs.setInt(1, 1);
cs.setInt(2, 5);
// Result is an java.sql.Array…
cs.registerOutParameter(3, Types.ARRAY, typeTableName);
cs.execute();

// Now who’s elements are java.sql.Structs
Object[] data = (Object[]) ((Array) cs.getObject(3)).getArray();
for(Object tmp : data) {
Struct row = (Struct) tmp;
// Attributes are index 1 based…
int idx = 1;
for(Object attribute : row.getAttributes()) {
System.out.print(metaData.getColumnName(idx) + ” = ” + attribute);

//Use the below switch block to populate into a POJO object.
switch (idx){
case 1 : System.out.println(” <<===>> as Column FirstName index = ” + idx + ” , value = ” + attribute.toString()); break;
case 2 : System.out.println(” <<===>> as Column LastName index = ” + idx + ” , value = ” + attribute.toString()); break;
case 3 : System.out.println(” <<===>> as Column Salary index = ” + idx + ” , value = ” + Integer.parseInt(“”+attribute.toString())); break;
case 4 : System.out.println(” <<===>> as Column Date Of Birth index = ” + idx + ” , value = ” + df.parse(attribute.toString())); break;
case 5 : System.out.println(” <<===>> as Column Remarks index = ” + idx + ” , value = ” + attribute.toString()); break;
}

++idx;

}
System.out.println(“—————————-“);
}
cs.close();
connection.close();
}
}

The output of the Java program will be:

FIRSTNAME = KHALEEL <<===>> as FirstName index = 1 , => KHALEEL
LASTNAME = SHAIK <<===>> as LastName index = 2 , => SHAIK
SALARY = 10000 <<===>> as Salary index = 3 , => 10000
DOB = 1989-04-20 15:44:36.0 <<===>> as DOB index = 4 , => Thu Apr 20 15:44:36 IST 1989
REMARKS = PRACTICE HEAD <<===>> as Remarks index = 5 , => PRACTICE HEAD
—————————-
FIRSTNAME = RAJA <<===>> as FirstName index = 1 , => RAJA
LASTNAME = RAO <<===>> as LastName index = 2 , => RAO
SALARY = 99000 <<===>> as Salary index = 3 , => 99000
DOB = 1992-01-15 15:44:36.0 <<===>> as DOB index = 4 , => Wed Jan 15 15:44:36 IST 1992
REMARKS = MANAGER <<===>> as Remarks index = 5 , => MANAGER
—————————-
FIRSTNAME = RANGA <<===>> as FirstName index = 1 , => RANGA
LASTNAME = RAJU <<===>> as LastName index = 2 , => RAJU
SALARY = 9000 <<===>> as Salary index = 3 , => 9000
DOB = 1988-10-02 15:44:36.0 <<===>> as DOB index = 4 , => Sun Oct 02 15:44:36 IST 1988
REMARKS = LEAD <<===>> as Remarks index = 5 , => LEAD
—————————-
FIRSTNAME = RAMANA <<===>> as FirstName index = 1 , => RAMANA
LASTNAME = REDDY <<===>> as LastName index = 2 , => REDDY
SALARY = 11000 <<===>> as Salary index = 3 , => 11000
DOB = 1989-01-10 15:44:36.0 <<===>> as DOB index = 4 , => Tue Jan 10 15:44:36 IST 1989
REMARKS = MANAGER PROJECTS <<===>> as Remarks index = 5 , => MANAGER PROJECTS
—————————-
FIRSTNAME = MOHAN <<===>> as FirstName index = 1 , => MOHAN
LASTNAME = RAO <<===>> as LastName index = 2 , => RAO
SALARY = 20000 <<===>> as Salary index = 3 , => 20000
DOB = 1989-01-10 15:44:36.0 <<===>> as DOB index = 4 , => Tue Jan 10 15:44:36 IST 1989
REMARKS = SOLARIS ADMIN <<===>> as Remarks index = 5 , => SOLARIS ADMIN
—————————-

== END ==

Feel free to email at [email protected] for any of your queries.

Read More