Collections of Scalar Types

Example Description

• Three collection types are declared.
• The stored procedure has three parameter of collections. A IN-parameter, a IN/OUT-parameter and a OUT-parameter.
• The stored procedure is called from the service with two Java list arguments and receives a Java transfer object with two Java lists as attributes.
• The OUT-parameter collection is filled with strings of the IN/OUT-parameter timestamp collection.
• In real world scenarios you will access tables in your database.

Important Notes !

• Allowed collections element types are : NUMBERS, VARCHARS, TIMESTAMP, DATE, XMLTYPE, SDO_GEOMETRY, object types and derived object types.
• Unsupported collection element types are : BFILE, PL/SQL boolean, PL/SQL table, PL/SQL record

Collections of Table of Element Type Timestamp

Collection Table Type : TABLE_OF_TIMESTAMP

/**
 * Table of timestamp
 */
create or replace type table_of_timestamp as table of timestamp;

Collections of VArray of Element Type Varchar2

Collection Varray Type : VARRAY_OF_VARCHAR

/**
 * Table of varchar2(100)
 */
create or replace type varray_of_varchar as varray(5) of varchar2(100);

Collections of Table of Element Type Number

Collection Varray Type : TABLE_OF_NUMBER

/**
 * Table of number
 */
create or replace type table_of_number as table of number(9);

PL/SQL Procedure

Collection Varray Type : COLLECTIONS_OF_SCALAR_TYPES

create or replace procedure collections_of_scalar_types
(
  i_col_number      in     table_of_number,
  io_col_timestamp  in out table_of_timestamp,
  o_col_varchar     out    varray_of_varchar
)
/**
 * Demonstration of calling a stored procedure with in/put parameter of collections of scalar element types.
 *
 * @param i_col_number Table-Collection of integer input values.
 * @param io_col_timestamp Table-Collection of timestamp value.
 * @param o_col_varchar Varray(5)-Collection of varchar2 out put values.
 */
is
begin
  for i in 1..greatest(io_col_timestamp.count, i_col_number.count) loop
    if (i <= io_col_timestamp.count) then
      if (i <= i_col_number.count) then
        -- add <n> days to timestamp of number list 
        io_col_timestamp(i) := io_col_timestamp(i) + i_col_number(i);
      else 
        -- add <n> days to timestamp from random value
        io_col_timestamp(i) := io_col_timestamp(i) + dbms_random.value(-3000,3000);
      end if;
    else
      -- add <n> days of current database systimestamp
      io_col_timestamp.extend();
      io_col_timestamp(i) := systimestamp + i_col_number(i);
    end if;
  end loop;
  
  -- fill string collection with formatted timestamp values
  o_col_varchar := varray_of_varchar();
  for i in 1..least(io_col_timestamp.count, o_col_varchar.limit) loop
    o_col_varchar.extend();
    o_col_varchar(i) := to_char(io_col_timestamp(i));
  end loop;
end collections_of_scalar_types;

Factory API : Calling the PL/SQL package

Java Calling Class : CollectionsOfScalarTypesFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import factory.ExamplesRPCFactory;
import service.CollectionsOfScalarTypesService;
import transferobject.CollectionsOfScalarTypesTO;

public class CollectionsOfScalarTypesFactoryApi {
  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
      CollectionsOfScalarTypesService service = ExamplesRPCFactory.getCollectionsOfScalarTypesService();

      // make some test data
      List<Integer> numberList = new ArrayList<>();
      List<Timestamp> timestampList = new ArrayList<>();

      for (int i = 0; i < 8; i++) {
        numberList.add(i);
      }

      for (int i = 0; i < 6; i++) {
        timestampList.add(new Timestamp(System.currentTimeMillis()));
      }

      // call the service
      CollectionsOfScalarTypesTO result = service.call(numberList, timestampList);

      // print result data
      for (Timestamp ts : result.ioColTimestamp) {
        System.out.println("ts:" + ts);
      }
      for (String s : result.oColVarchar) {
        System.out.println(s);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : CollectionsOfScalarTypesSpringApi.java

package plsql_workbench_examples.springapi;

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.CollectionsOfScalarTypesService;
import transferobject.CollectionsOfScalarTypesTO;

@Component
public class CollectionsOfScalarTypesSpringApi {
  @Autowired
  private CollectionsOfScalarTypesService collectionsOfScalarTypesService;

  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(CollectionsOfScalarTypesSpringApi.class)) {
      ctx.getBean(CollectionsOfScalarTypesSpringApi.class).runDemo();
    }
  }

  private void runDemo()
  {
    try {
      // make some test data
      List<Integer> numberList = new ArrayList<Integer>();
      List<Timestamp> timestampList = new ArrayList<Timestamp>();

      for (int i = 0; i < 8; i++) {
        numberList.add(i);
      }

      for (int i = 0; i < 6; i++) {
        timestampList.add(new Timestamp(System.currentTimeMillis()));
      }

      // call the service
      CollectionsOfScalarTypesTO result = collectionsOfScalarTypesService.call(numberList, timestampList);

      // print result data
      for (Timestamp ts : result.getIoColTimestamp()) {
        System.out.println("ts:" + ts);
      }
      for (String s : result.getOColVarchar()) {
        System.out.println(s);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}