Some times its necessary that we get the data from the Oracle PL/SQL procedures as a TABLE type objects. Retrieving the data from the table type objects is not same as we retrieve the data from ResultSet. So, here a complete example is given for the benefit of PL/SQL and Java developers.
1. Create a table EMPLOYEE
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 ==
No comments:
Post a Comment