Typed Reference Cursor of PL/SQL Record

Example Description

• Package specification :
  • A PL/SQL record type defines the row information of the cursor.
  • The PL/SQL record type is used to generate a Java transfer object class.
    The Java attributes are camel case named like the PL/SQL record attribute type.
  • A cursor type is needed in the stored procedure to open the cursor.
  • The cursor will be converted to a Java list of Java transfer object class.
• Package body just has the stored procedure function implementation.
• The Java code based on the Java RPC Connector Builder just gets the service and calls each function.
  • The factory has a static method getScalarTypesService() returning the service.
  • The service has two methods addNum(..) and concatChar(..) to call the stored procedures.

Important Notes !

• You can reference to cursor types declared in other packages.
• If you define only one element in the record, no Java transfer object class is created, you receive a Java list of the scalar value.

Package Specification

PL/SQL Package Specification : TYPED_REF_CURSOR_PLSQL_RECORD

create or replace package TYPED_REF_CURSOR_PLSQL_RECORD
/**
 * Typed Ref Cursor of PL/SQL RECORD Demo.
 */
as

-- this type of PL/SQL record will be treated as scalar value
type rec_product is record (
  product   varchar2(100)
);

-- this typed ref cursor represents a list of Java strings
type c_product is ref cursor return rec_product;

-- 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 products.
 *
 * @return List of installed products.
 */
function get_product return c_product;

/**
 * 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_PLSQL_RECORD;

Package Body

PL/SQL Package Specification : TYPED_REF_CURSOR_PLSQL_RECORD

create or replace package body TYPED_REF_CURSOR_PLSQL_RECORD
as

/**
 * Return a list of installed database products.
 *
 * @return List of installed products.
 */
function get_product return c_product
is
  c c_product;
begin
  open c for
    select  trim(product)
      from  product_component_version;
      
  return c;
end get_product;

/**
 * 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
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_PLSQL_RECORD;

Factory API : Calling the PL/SQL package

Java Calling Class : TypedRefCursorPlSqlRecordFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.util.List;

import factory.ExamplesRPCFactory;
import service.TypedRefCursorPlsqlRecordService;
import transferobject.TypedRefCursorPlsqlRecordTO;

public class TypedRefCursorPlSqlRecordFactoryApi {
  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
      TypedRefCursorPlsqlRecordService service = ExamplesRPCFactory.getTypedRefCursorPlsqlRecordService();

      // call the stored procedure and receive a list of strings
      List<String> productList = service.getProduct(0);

      // print information
      System.out.println("Product list:");
      for (String product : productList) {
        System.out.format("%s%n", product);
      }

      // call the stored procedure and receive a list of transfer objects
      List<TypedRefCursorPlsqlRecordTO.RecProductVersionStatus> infoList = service.getProductVersionStatus(0);

      // print information
      System.out.println("\nInstalled products, version and status:");
      for (TypedRefCursorPlsqlRecordTO.RecProductVersionStatus info : infoList) {
        System.out.format("product[%s]  version[%s]  status[%s]%n", info.product, info.version, info.status);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : TypedRefCursorPlSqlRecordSpringApi.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.TypedRefCursorPlsqlRecordService;
import transferobject.TypedRefCursorPlsqlRecordTO;

@Component
public class TypedRefCursorPlSqlRecordSpringApi {
  @Autowired
  private TypedRefCursorPlsqlRecordService typedRefCursorPlsqlRecordService;

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

  private void runDemo()
  {
    try {
      // call the stored procedure and receive a list of strings
      List<String> productList = typedRefCursorPlsqlRecordService.getProduct(0);

      // print information
      System.out.println("Product list:");
      for (String product : productList) {
        System.out.format("%s%n", product);
      }

      // call the stored procedure and receive a list of transfer objects
      List<TypedRefCursorPlsqlRecordTO.RecProductVersionStatus> infoList;
      infoList = typedRefCursorPlsqlRecordService.getProductVersionStatus(0);

      // print information
      System.out.println("\nInstalled products, version and status:");
      for (TypedRefCursorPlsqlRecordTO.RecProductVersionStatus info : infoList) {
        System.out.format("product[%s]  version[%s]  status[%s]%n",
                          info.getProduct(),
                          info.getVersion(),
                          info.getStatus());
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}