In most of our application where a procedure is written, most often than not we would have to extract the response of the oracle pl/sql call from its out parameters. Below is a small code snippet which can guide you to get that data which you need. There can be small changes depending on the jdbc framework you use. I have copied it from my project code which I had written using Spring JDBC and Oracle.
For Spring JDBC developers, the first 3 lines show how we can execute and extract the out object. The remaining lines of code is common for any framework.
Map outputMap = simpleJdbcCallObject.execute(map);
String result = (String) outputMap.get("O_RETURN_STATUS");
java.sql.Struct permissionStatus = (java.sql.Struct) outputMap
.get("P_RECORD_STATUS_OBJ");
Now that we have the Struct, let us see a scenario where we have a table object which contains a array of oracle objects. This table object is wrapped inside a wrapper object. (It is better to wrap the table inside a wrapper object which will be recieved as a Struct rather than getting the table object directly from the procedure call)
List recordStatusResult = new ArrayList();
try {
//Get the attributes from the wrapper object (Struct). We may have more than one table/elements inside the wrapper object. but in this case, we have only one table object
Object[] tableAttributeArray = permissionStatus.getAttributes();
//Make sure the the wrapper object is not null or empty
if (tableAttributeArray == null or tableAttributeArray.length < 1
or tableAttributeArray[0] == null) {
throw new Exception(
"No table data found in the response from the pl/sql procedure");
}
//Get the table object from the attributes, which is of type oracle.sql.ARRAY
ARRAY tableAttribute = (ARRAY) tableAttributeArray[0];
//Get the records from the table. Each record will be a object which is required for us.
Object[] structArray = (Object[]) tableAttribute.getArray();
//Make sure the table has atleast one record. You can skip this if not required to check
if (structArray == null or structArray.length < 1) {
throw new Exception(
"No Object data found in the response from the pl/sql procedure");
}
//Loop throw all the records to get the data.
Object struct;
for (int i = 0; i < structArray.length; i++) {
//Get the ith record from the table
struct = structArray[i];
//Each record will have one or more elements or properties
Object[] permissionData = ((Struct) struct).getAttributes();
//In my case, I store it in a list, you can extract the value like String s = (String)permissionData[0];
recordStatusResult.add(permissionData);
}
} catch (SQLException e) {
throw new Exception(
"Error while fetching the data from the pl/sql output.",
e);
}
The code above has comments which explain each step, so I believe no more explanation is required.
I need to return the data in a list, hence I am storing each object array record in the list.
