Typed Reference Cursor of Collection

Example Description

• A cursor element must be of type PL/SQL Record.
• Any attribute of the PL/SQL Record can define an user defined type.
• If the PL/SQL Record defines only one attribute the result cursor will be a Java list of objects defined by the user defined type.
• If the PL/SQL Record defines more than one attribute the result cursor will be a Java list of transfer objects defined from the PL/SQL Record. The user defined type attribute of the PL/SQL Record will be transfer object corresponding to the user defined type.
• If the user defined type collection the result of the typed cursor is a Java List of Java List.

Object Definition

PL/SQL Package Specification : REFCUR_OBJECT

/**
 * REFCUR_OBJECT for typed ref cursor examples.
 */
create or replace type refcur_object force as object (
  n           number(9),
  v           varchar2(100)
);

Collection Definition

PL/SQL Package Specification : REFCUR_COLLECTION

/**
 * REFCUR_COLLECTION for typed ref cursor examples.
 */
create or replace type refcur_collection force as table of refcur_object;

Package Specification

PL/SQL Package Specification : TYPED_REF_CURSOR_COLLECTION

create or replace package TYPED_REF_CURSOR_COLLECTION
/**
 * Typed Ref Cursor of Collection Demo.
 */
as

-- this type of PL/SQL record will be treated as list of object/transfer object value
type rec_refcur_collection is record (
  o   refcur_collection
);

-- this typed ref cursor represents a list of list of object/transfer object values
type c_refcur_collection is ref cursor return rec_refcur_collection;

-- this type of PL/SQL record will be treated as object/transfer object value
type rec_refcur_collection_mixed is record (
  ts            timestamp,
  row_number_id number,
  refcur_list   refcur_collection
);

-- this typed ref cursor represents a list of transfer objects containing a list element
type c_refcur_collection_mixed is ref cursor return rec_refcur_collection_mixed;

/**
 * Return a list of list of objects/transfer objects.
 *
 * @return List of list of objects/transfer objects.
 */
function get_refcur_collection(i_number_of_rows in number) return c_refcur_collection;

/**
 * Return a list of pl/sql records/transfer objects including a list of objects.
 *
 * @return List of pl/sql records/transfer objects including a list of objects.
 */
function get_refcur_collection_mixed(i_number_of_rows in number) return c_refcur_collection_mixed;

end TYPED_REF_CURSOR_COLLECTION;

Package Body

PL/SQL Package Specification : TYPED_REF_CURSOR_COLLECTION

create or replace package body TYPED_REF_CURSOR_COLLECTION
as

/**
 * Return a list of list of objects/transfer objects.
 *
 * @return List of list of objects/transfer objects.
 */
function get_refcur_collection(i_number_of_rows in number) return c_refcur_collection
is
  c c_refcur_collection;
begin
  open c for
    with row_number_id as 
      (select rownum id from dual connect by level<=i_number_of_rows)
    select  cast(multiset(select refcur_object(row_number_id.id, 'element refcur_object no. ' || rownum)
                            from dual
                            connect by level<=i_number_of_rows) as refcur_collection)
      from  row_number_id;

  return c;
end get_refcur_collection;

/**
 * Return a list of pl/sql records/transfer objects including a list of objects.
 *
 * @return List of pl/sql records/transfer objects including a list of objects.
 */
function get_refcur_collection_mixed(i_number_of_rows in number) return c_refcur_collection_mixed
is
  c c_refcur_collection_mixed;
begin
  open c for
    with row_number_id as 
      (select rownum id, (systimestamp+rownum*123.456) ts from dual connect by level<=i_number_of_rows)
    select  row_number_id.ts,
            row_number_id.id,
            cast(multiset(select refcur_object(row_number_id.id, 'element refcur_object no. ' || rownum)
                            from dual
                            connect by level<=i_number_of_rows) as refcur_collection)
      from  row_number_id;

  return c;
end get_refcur_collection_mixed;

end TYPED_REF_CURSOR_COLLECTION;

Factory API : Calling the PL/SQL package

Java Calling Class : TypedRefCursorCollectionFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.util.List;
import java.util.concurrent.ThreadLocalRandom;

import factory.ExamplesRPCFactory;
import service.TypedRefCursorCollectionService;
import transferobject.RefcurObject;
import transferobject.TypedRefCursorCollectionTO;
import transferobject.TypedRefCursorCollectionTO.RecRefcurCollectionMixed;

public class TypedRefCursorCollectionFactoryApi {
  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
      TypedRefCursorCollectionService service = ExamplesRPCFactory.getTypedRefCursorCollectionService();

      // call the stored procedure and receive a list of list as result
      List<List<RefcurObject>> listOfList;
      listOfList = service.getRefcurCollection(ThreadLocalRandom.current().nextInt(2, 8), 0);

      // print information
      int row = 1;
      for (List<RefcurObject> list : listOfList) {
        System.out.println("row:" + (row++));
        for (RefcurObject obj : list) {
          System.out.format("element RefcurObject[n:%s, v:'%s']%n", obj.n, obj.v);
        }
      }

      // call the stored procedure and receive a list of PL/SQL record/transfer objects containing a collection/list
      List<TypedRefCursorCollectionTO.RecRefcurCollectionMixed> mixedList;
      mixedList = service.getRefcurCollectionMixed(ThreadLocalRandom.current().nextInt(2, 8), 0);

      // print information
      for (RecRefcurCollectionMixed mixedElement : mixedList) {
        System.out.format("row element [id:%s, ts:%s]%n", mixedElement.rowNumberId, mixedElement.ts);
        for (RefcurObject obj : mixedElement.refcurList) {
          System.out.format("\tlist element RefcurObject[n:%s, v:'%s']%n", obj.n, obj.v);
        }
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : TypedRefCursorCollectionSpringApi.java

package plsql_workbench_examples.springapi;

import java.util.List;
import java.util.concurrent.ThreadLocalRandom;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;

import service.TypedRefCursorCollectionService;
import transferobject.RefcurObject;
import transferobject.TypedRefCursorCollectionTO;
import transferobject.TypedRefCursorCollectionTO.RecRefcurCollectionMixed;

@Component
public class TypedRefCursorCollectionSpringApi {
  @Autowired
  private TypedRefCursorCollectionService typedRefCursorCollectionService;

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

  private void runDemo()
  {
    try {
      // call the stored procedure and receive a list of list as result
      List<List<RefcurObject>> listOfList;
      listOfList = typedRefCursorCollectionService.getRefcurCollection(ThreadLocalRandom.current().nextInt(2, 8), 0);

      // print information
      int row = 1;
      for (List<RefcurObject> list : listOfList) {
        System.out.println("row:" + (row++));
        for (RefcurObject obj : list) {
          System.out.format("element RefcurObject[n:%s, v:'%s']%n", obj.getN(), obj.getV());
        }
      }

      // call the stored procedure and receive a list of PL/SQL record/transfer objects containing a collection/list
      List<TypedRefCursorCollectionTO.RecRefcurCollectionMixed> mixedList;
      mixedList = typedRefCursorCollectionService.getRefcurCollectionMixed(ThreadLocalRandom.current().nextInt(2, 8),
                                                                           0);

      // print information
      for (RecRefcurCollectionMixed mixedElement : mixedList) {
        System.out.format("row element [id:%s, ts:%s]%n", mixedElement.getRowNumberId(), mixedElement.getTs());
        for (RefcurObject obj : mixedElement.getRefcurList()) {
          System.out.format("\tlist element RefcurObject[n:%s, v:'%s']%n", obj.getN(), obj.getV());
        }
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}