< Prev - Examples - Next >

Typed Reference Cursor

This is the easiest way to open a select cursor and receive a list of structured data from the database.

Example Description

Important Notes !

Package Specification

The package 'TYPED_REF_CURSOR' defines a function returning a typed ref cursor of record type 'REC_PRODUCT_VERSION_STATUS'.
PL/SQL Package Specification : TYPED_REF_CURSOR
create or replace package typed_ref_cursor
/**
 * Typed Ref Cursor Demo.
 */
as

-- this type of PL/SQL record will create a transfer object
type rec_product_version_status is record (
  product   varchar2(100),
  version   varchar2(100),
  status    varchar2(100)
);

-- this typed ref cursor represents a list of transfer objects
type c_product_version_status is ref cursor return rec_product_version_status;

/**
 * Return a list of installed database components.
 *
 * @return List of installed components of PL/SQL record type rec_product_version_status.
 */
function get_product_version_status return c_product_version_status;

end typed_ref_cursor;

Package Body

The package 'TYPED_REF_CURSOR' implements a function returning a typed ref cursor of record type 'REC_PRODUCT_VERSION_STATUS'.
PL/SQL Package Specification : TYPED_REF_CURSOR
create or replace package body typed_ref_cursor
as

function get_product_version_status return c_product_version_status
is
  c c_product_version_status;
begin
  open c for
    select  trim(product),
            trim(version),
            trim(status)
      from  product_component_version;
      
  return c;
end get_product_version_status;

end typed_ref_cursor;

Factory API : Calling the PL/SQL package

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

import java.util.List;

import factory.ExamplesRPCFactory;
import service.TypedRefCursorService;
import transferobject.TypedRefCursorTO;

public class TypedRefCursorFactoryApi {
  public static void main(String[] args) {
    try {
      // get the service
      TypedRefCursorService service = ExamplesRPCFactory.getTypedRefCursorService();

      // call the stored procedure and receive a list as result
      List<TypedRefCursorTO.RecProductVersionStatus> infoList = service.getProductVersionStatus(0);

      // print information
      for (TypedRefCursorTO.RecProductVersionStatus info : infoList) {
        System.out.println(String.format("%-40s  %s  %s", info.product, info.version, info.status));
      }
    }
    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 : TypedRefCursorSpringApi.java
package plsql_workbench_examples.springapi;

import java.util.List;

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

import service.TypedRefCursorService;
import transferobject.TypedRefCursorTO;

@Component
public class TypedRefCursorSpringApi {
  @Autowired
  private TypedRefCursorService typedRefCursorService;

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

  private void runDemo() {
    try {
      // call the stored procedure and receive a list as result
      List<TypedRefCursorTO.RecProductVersionStatus> infoList = typedRefCursorService.getProductVersionStatus(0);

      // print information
      for (TypedRefCursorTO.RecProductVersionStatus info : infoList) {
        System.out.println(String.format("%-40s  %s  %s", info.getProduct(), info.getVersion(), info.getStatus()));
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}