< Prev - Examples - Next >

Collections Of Objects

Example Description

Important Notes !

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.
Collection Varray Type : 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 {
      // get the service
      CollectionsOfObjectsService service = ExamplesRPCFactory.getCollectionsOfObjectsService();

      // create some test data
      List<ColObject> objectList = new ArrayList<ColObject>();
      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.println(String.format("d[%s] ts[%s] s[%s]", 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) {
    // 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.println(String.format("d[%s] ts[%s] s[%s]", o.getD(), o.getTs(), o.getS()));
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}