Collections of Objects
Example Description
- An object type and a collection type of the object type is declared.
- The stored procedure has an IN-parameter and a OUT-parameter of the object typed collection and fills the elements of the output collection with same values of the elements of the input collection.
User Defined Type of Type Object
Object Type Specification : COL_OBJECT
/**
* Object type with three fields of date, timestamp and varchar2.
*/
create or replace type col_object force as object (
d date,
ts timestamp,
s varchar2(100)
);
User Defined Type of Type of Collection of Element Type Object 'COL_TYPE'
Collection Table Type : TABLE_OF_COL_OBJECT
/**
* Table of type col_object.
*/
create or replace type table_of_col_object as table of col_object;
PL/SQL Procedure
The procedure 'COLLECTIONS_OF_OBJECTS' demonstrates how to work with collections of type object as input and output parameter.PL/SQL Procedure : COLLECTIONS_OF_OBJECTS
create or replace procedure collections_of_objects
(
i_delta_d in number,
i_delta_ts in number,
i_col_objects in table_of_col_object,
o_col_objects out table_of_col_object
)
/**
* Demonstration of calling a stored procedure with input of table of object elements. The elements are modified and returned as out put parameter.
*
* @param i_delta_d Offset is added to date value.
* @param i_delta_ts Offset is added to timestamp value.
* @param i_col_objects Input list of collection objects.
* @param o_col_objects Output list of collection objects.
*/
is
begin
-- initialize output collection
o_col_objects := table_of_col_object();
-- append elements to collection
for i in 1..i_col_objects.count loop
o_col_objects.extend();
o_col_objects(i) := col_object(i_col_objects(i).d + i_delta_d + i, i_col_objects(i).ts + i_delta_ts + i, i_col_objects(i).s || ' - ' || DBMS_RANDOM.string('A',10));
end loop;
end collections_of_objects;
Factory API : Calling the PL/SQL package
Using the static factory to get the remote service.Java Calling Class : CollectionsOfObjectsFactoryApi.java
package plsql_workbench_examples.factoryapi;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import factory.ExamplesRPCFactory;
import service.CollectionsOfObjectsService;
import transferobject.ColObject;
public class CollectionsOfObjectsFactoryApi {
public static void main(String[] args)
{
try {
// set database credentials and configuration parameters
System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
System.setProperty("dbw_examples.username", "dbw_examples");
System.setProperty("dbw_examples.password", "dbw_examples");
// get the service
CollectionsOfObjectsService service = ExamplesRPCFactory.getCollectionsOfObjectsService();
// create some test data
List<ColObject> objectList = new ArrayList<>();
for (int i = 0; i < 3; i++) {
ColObject o = new ColObject();
o.d = new Date(System.currentTimeMillis());
o.s = "init value";
o.ts = new Timestamp(System.currentTimeMillis());
objectList.add(o);
}
// call the service and receive result list of transfer objects
List<ColObject> resultList = service.call(3, 7, objectList);
// printing the result list
for (ColObject o : resultList) {
System.out.format("d[%s] ts[%s] s[%s]%n", o.d, o.ts, o.s);
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}
Spring API : Calling the PL/SQL package
Using Spring annotation to inject the service and call the remote service.Java Calling Class : CollectionsOfObjectsSpringApi.java
package plsql_workbench_examples.springapi;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;
import service.CollectionsOfObjectsService;
import transferobject.ColObject;
@Component
public class CollectionsOfObjectsSpringApi {
@Autowired
private CollectionsOfObjectsService collectionsOfObjectsService;
public static void main(String[] args)
{
// set database credentials and configuration parameters
System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
System.setProperty("dbw_examples.username", "dbw_examples");
System.setProperty("dbw_examples.password", "dbw_examples");
// Register Spring Beans, Spring Context and call demo method
try (GenericApplicationContext ctx = BaseSpringConfig.getCtx(CollectionsOfObjectsSpringApi.class)) {
ctx.getBean(CollectionsOfObjectsSpringApi.class).runDemo();
}
}
private void runDemo()
{
try {
// create some test data
List<ColObject> objectList = new ArrayList<ColObject>();
for (int i = 0; i < 3; i++) {
ColObject o = new ColObject();
o.setD(new Date(System.currentTimeMillis()));
o.setS("init value");
o.setTs(new Timestamp(System.currentTimeMillis()));
objectList.add(o);
}
// call the service and receive result list of transfer objects
List<ColObject> resultList = collectionsOfObjectsService.call(3, 7, objectList);
// printing the result list
for (ColObject o : resultList) {
System.out.format("d[%s] ts[%s] s[%s]%n", o.getD(), o.getTs(), o.getS());
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}