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

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

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

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();
    }
  }
}