Collections of PL/SQL Record

Example Description

• Using a PL/SQL Record as collection element.

PL/SQL Package Specification

PL/SQL Package Specification : COLLECTIONS_OF_PLSQL_RECORDS

create or replace package collection_of_plsql_records
as
/**
 * Demonstration of calling in-package defined collections of element type PL/SQL Record.
 */

type plsql_record is record (
  d           date,
  ts          timestamp,
  s           varchar2(100)
);
type table_plsql_record is table of plsql_record;

/**
 * Demonstration of calling a stored procedure with input of table of PL/SQL record elements. The elements are modified and returned as output parameter.
 *
 * @param i_delta_d Offset is added to date value.
 * @param i_delta_ts Offset is added to timestamp value.
 * @param i_table_plsql_record Input list of collection PL/SQL Records.
 * @param o_table_plsql_record Output list of collection PL/SQL Records.
 */
procedure doit
(
  i_delta_d             in    number,
  i_delta_ts            in    number,
  i_table_plsql_record  in    table_plsql_record,
  o_table_plsql_record  out   table_plsql_record
);
  
end collection_of_plsql_records;

PL/SQL Package Implementation

PL/SQL Package Body : COLLECTIONS_OF_PLSQL_RECORDS

create or replace package body collection_of_plsql_records
as

/**
 * Demonstration of calling a stored procedure with input of table of PL/SQL record elements. The elements are modified and returned as output parameter.
 *
 * @param i_delta_d Offset is added to date value.
 * @param i_delta_ts Offset is added to timestamp value.
 * @param i_table_plsql_record Input list of collection PL/SQL Records.
 * @param o_table_plsql_record Output list of collection PL/SQL Records.
 */
procedure doit
(
  i_delta_d             in    number,
  i_delta_ts            in    number,
  i_table_plsql_record  in    table_plsql_record,
  o_table_plsql_record  out   table_plsql_record
)
is
begin
  o_table_plsql_record := table_plsql_record();
  
  -- append elements to collection
  for i in 1..i_table_plsql_record.count loop
    o_table_plsql_record.extend();
    o_table_plsql_record(i).d := i_table_plsql_record(i).d + i_delta_d + i;
    o_table_plsql_record(i).ts := i_table_plsql_record(i).ts + i_delta_ts + i;
    o_table_plsql_record(i).s := i_table_plsql_record(i).s || ' - ' || DBMS_RANDOM.string('A',10);
  end loop;
end doit;  


end collection_of_plsql_records;

Factory API : Calling the PL/SQL package

Java Calling Class : CollectionsOfPlSqlRecordsFactoryApi.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.CollectionOfPlsqlRecordsService;
import transferobject.CollectionOfPlsqlRecordsTO;
import transferobject.CollectionOfPlsqlRecordsTO.PlsqlRecord;

public class CollectionsOfPlSqlRecordsFactoryApi {
  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
      CollectionOfPlsqlRecordsService service = ExamplesRPCFactory.getCollectionOfPlsqlRecordsService();

      // create some test data
      List<CollectionOfPlsqlRecordsTO.PlsqlRecord> list = new ArrayList<>();
      for (int i = 0; i < 3; i++) {
        CollectionOfPlsqlRecordsTO.PlsqlRecord o = new CollectionOfPlsqlRecordsTO.PlsqlRecord();
        o.d = new Date(System.currentTimeMillis());
        o.s = "init value";
        o.ts = new Timestamp(System.currentTimeMillis());
        list.add(o);
      }

      // call the service and receive result list of transfer objects
      List<CollectionOfPlsqlRecordsTO.PlsqlRecord> resultList = service.doit(3, 7, list);

      // printing the result list
      for (PlsqlRecord record : resultList) {
        System.out.format("d[%s] ts[%s] s[%s]%n", record.d, record.ts, record.s);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : CollectionsOfPlSqlRecordsSpringApi.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.CollectionOfPlsqlRecordsService;
import transferobject.CollectionOfPlsqlRecordsTO;
import transferobject.CollectionOfPlsqlRecordsTO.PlsqlRecord;

@Component
public class CollectionsOfPlSqlRecordsSpringApi {
  @Autowired
  private CollectionOfPlsqlRecordsService collectionOfPlsqlRecordsService;

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

  public void runDemo()
  {
    try {
      // create some test data
      List<CollectionOfPlsqlRecordsTO.PlsqlRecord> list = new ArrayList<>();
      for (int i = 0; i < 3; i++) {
        CollectionOfPlsqlRecordsTO.PlsqlRecord o = new CollectionOfPlsqlRecordsTO.PlsqlRecord();
        o.setD(new Date(System.currentTimeMillis()));
        o.setS("init value");
        o.setTs(new Timestamp(System.currentTimeMillis()));
        list.add(o);
      }

      // call the service and receive result list of transfer objects
      List<CollectionOfPlsqlRecordsTO.PlsqlRecord> resultList = collectionOfPlsqlRecordsService.doit(3, 7, list);

      // printing the result list
      for (PlsqlRecord record : resultList) {
        System.out.format("d[%s] ts[%s] s[%s]%n", record.getD(), record.getTs(), record.getS());
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}