< Prev - Examples - Next >

Typed Reference Cursor of Object

Fetch a cursor of user defined type 'REFCUR_OBJECT'.

Example Description

Object Definition

The user defined type 'REFCUR_OBJECT' is used as cursor element.
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)
);

Package Specification

PL/SQL Package Specification : TYPED_REF_CURSOR_OBJECT
create or replace package TYPED_REF_CURSOR_OBJECT
/**
 * Typed Ref Cursor of Object Demo.
 */
as

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

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

-- this type of PL/SQL record will be treated as object/transfer object value
type rec_refcur_object_mixed is record (
  ts  timestamp,
  o   refcur_object
);

-- this typed ref cursor represents a list of transfer objects
type c_refcur_object_mixed is ref cursor return rec_refcur_object_mixed;

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

/**
 * Return a list of pl/sql records/transfer objects including an object.
 *
 * @return List of pl/sql records/transfer objects including an object.
 */
function get_refcur_object_mixed(i_number_of_rows in number) return c_refcur_object_mixed;

end TYPED_REF_CURSOR_OBJECT;

Package Body

PL/SQL Package Specification : TYPED_REF_CURSOR_OBJECT
create or replace package body TYPED_REF_CURSOR_OBJECT
as

/**
 * Return a list of objects/transfer objects.
 *
 * @return List of objects/transfer objects.
 */
function get_refcur_object(i_number_of_rows in number) return c_refcur_object
is
  c c_refcur_object;
begin
  open c for
    select  refcur_object(rownum, 'element refcur_object no. ' || rownum)
      from  dual 
      connect by level<=i_number_of_rows;

  return c;
end get_refcur_object;

/**
 * Return a list of pl/sql records/transfer objects including an object.
 *
 * @return List of pl/sql records/transfer objects including an object.
 */
function get_refcur_object_mixed(i_number_of_rows in number) return c_refcur_object_mixed
is
  c c_refcur_object_mixed;
begin
  open c for
    select  systimestamp+rownum * 12.3456,
            refcur_object(rownum, 'element refcur_object no. ' || rownum)
      from  dual 
      connect by level<=i_number_of_rows;

  return c;
end get_refcur_object_mixed;

end TYPED_REF_CURSOR_OBJECT;

Factory API : Calling the PL/SQL package

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

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

import factory.ExamplesRPCFactory;
import service.TypedRefCursorObjectService;
import transferobject.RefcurObject;
import transferobject.TypedRefCursorObjectTO;

public class TypedRefCursorObjectFactoryApi {
  public static void main(String[] args) {
    try {
      // get the service
      TypedRefCursorObjectService service = ExamplesRPCFactory.getTypedRefCursorObjectService();

      // call the stored procedure and receive a list of objects
      List<RefcurObject> list;
      list = service.getRefcurObject(ThreadLocalRandom.current().nextInt(5, 13), 0);

      // print information
      for (RefcurObject item : list) {
        System.out.format("RefcurObject[n:%s, v:%s]%n", item.n, item.v);
      }

      // call the stored procedure and receive a list of mixed objects
      List<TypedRefCursorObjectTO.RecRefcurObjectMixed> mixedList;
      mixedList = service.getRefcurObjectMixed(ThreadLocalRandom.current().nextInt(7, 17), 0);

      // print information
      for (TypedRefCursorObjectTO.RecRefcurObjectMixed item : mixedList) {
        System.out.format("ts:%s - RefcurObject[n:%s, v:%s]%n", item.ts, item.o.n, item.o.v);
      }
    }
    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 : TypedRefCursorObjectSpringApi.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.TypedRefCursorObjectService;
import transferobject.RefcurObject;
import transferobject.TypedRefCursorObjectTO;

@Component
public class TypedRefCursorObjectSpringApi {
  @Autowired
  private TypedRefCursorObjectService typedRefCursorObjectService;

  public static void main(String[] args) {
    // Register Spring Beans, Spring Context and call demo method 
    try (GenericApplicationContext ctx = BaseSpringConfig.getCtx(TypedRefCursorObjectSpringApi.class)) {
      ctx.getBean(TypedRefCursorObjectSpringApi.class).runDemo();
    }
  }

  private void runDemo() {
    try {
      // call the stored procedure and receive a list of objects
      List<RefcurObject> list = typedRefCursorObjectService.getRefcurObject(ThreadLocalRandom.current().nextInt(5, 13),
                                                                            0);

      // print information
      for (RefcurObject item : list) {
        System.out.format("RefcurObject[n:%s, v:%s]%n", item.getN(), item.getV());
      }

      // call the stored procedure and receive a list of mixed objects
      List<TypedRefCursorObjectTO.RecRefcurObjectMixed> mixedList;
      mixedList = typedRefCursorObjectService.getRefcurObjectMixed(ThreadLocalRandom.current().nextInt(7, 17), 0);

      // print information
      for (TypedRefCursorObjectTO.RecRefcurObjectMixed item : mixedList) {
        System.out.format("ts:%s - RefcurObject[n:%s, v:%s]%n", item.getTs(), item.getO().getN(), item.getO().getV());
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

< Prev - Examples - Next >