Collections of Scalar Types - In-Package Definition

This example is a demonstration of using collections defined inside the package specification. The next example demonstrate the same functionality of collections defined as user type.

Example Description

  • A simple PL/SQL package with one stored procedure.
  • Three collection types are declared inside the package specification.
  • 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.
  • In real world scenarios you will access tables in your database.

Important Notes !

  • The JDBC call of in-package defined collections is only supported under Oracle 12c - it is not supported under Oracle 11g
  • Allowed collections in-package defined scalar element types are : NUMBERS, VARCHARS, TIMESTAMP, DATE, XMLTYPE, BLOB, CLOB, SDO_GEOMETRY.
  • Unsupported collection element types are : BFILE, PL/SQL boolean, PL/SQL table, PL/SQL record

Package Specification

The package 'COLLECTION_IN_PACKAGE' defines a procedure working with collections as input and output parameter.
PL/SQL Package Specification : COLLECTION_IN_PACKAGE
create or replace package collection_in_package
as
/**
 * Demonstration of calling in-package defined collections.
 */

type number_table_in_package is table of number(9);
type timestamp_table_in_package is table of timestamp;
type varchar_varray_in_package is varray(5) of varchar2(100);

/**
 * 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 input/output values.
 * @param o_col_varchar Varray(5)-Collection of varchar2 output values.
 */
procedure do_it
(
  i_col_number      in     number_table_in_package,
  io_col_timestamp  in out timestamp_table_in_package,
  o_col_varchar     out    varchar_varray_in_package
);

end collection_in_package;

Package Body

The package 'COLLECTION_IN_PACKAGE' implements a procedure working with collections as input and output parameter.
PL/SQL Package Body : COLLECTION_IN_PACKAGE
create or replace package body collection_in_package
as

procedure do_it
(
  i_col_number      in     number_table_in_package,
  io_col_timestamp  in out timestamp_table_in_package,
  o_col_varchar     out    varchar_varray_in_package
)
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 := varchar_varray_in_package();
  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 do_it;

end collection_in_package;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.
Java Calling Class : CollectionsOfScalarInPackageTypesFactoryApi.java
package plsql_workbench_examples.factoryapi;

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

import factory.ExamplesRPCFactory;
import service.CollectionInPackageService;
import transferobject.CollectionInPackageTO;

public class CollectionsOfScalarInPackageTypesFactoryApi {
  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
      CollectionInPackageService service = ExamplesRPCFactory.getCollectionInPackageService();

      // 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
      CollectionInPackageTO.DoItTO result = service.doIt(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

Using Spring annotation to inject the service and call the remote service.
Java Calling Class : CollectionsOfScalarInPackageTypesSpringApi.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.CollectionInPackageService;
import transferobject.CollectionInPackageTO;

@Component
public class CollectionsOfScalarInPackageTypesSpringApi {
  @Autowired
  private CollectionInPackageService collectionInPackageService;

  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(CollectionsOfScalarInPackageTypesSpringApi.class)) {
      ctx.getBean(CollectionsOfScalarInPackageTypesSpringApi.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
      CollectionInPackageTO.DoItTO result = collectionInPackageService.doIt(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();
    }
  }
}