< Prev - Examples - Next >

Typed Reference Cursor of Rowtype

Example Description

Fetch a cursor of Table 'ROWTYPE_EXAMPLE%ROWTYPE'.

Package Specification

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

-- this typed ref cursor represents a list of transfer objects
type c_rowtype_example is ref cursor return rowtype_example%rowtype;

/**
 * Return a list of rowtype elements.
 *
 * @return List of rowtype elements.
 */
function get_rowtype_example(i_number_of_rows in number) return c_rowtype_example;

end TYPED_REF_CURSOR_ROWTYPE;

Package Body

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

/**
 * Return a list of rowtype elements.
 *
 * @return List of rowtype elements.
 */
function get_rowtype_example(i_number_of_rows in number) return c_rowtype_example
is
  c c_rowtype_example;
begin
  open c for 
    select rownum,
           rownum * 1.234,
           systimestamp + rownum * 12.3456,
           sysdate + rownum * 17,
           to_char(systimestamp+rownum*2.3456) || ' -> ' || rownum
      from  dual 
      connect by level<=i_number_of_rows;
      
  return c;
end get_rowtype_example;

end TYPED_REF_CURSOR_ROWTYPE;

Factory API : Calling the PL/SQL package

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

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

import factory.ExamplesRPCFactory;
import service.TypedRefCursorRowtypeService;
import transferobject.RowtypeExample;

public class TypedRefCursorRowtypeFactoryApi {
  public static void main(String[] args) {
    try {
      // get the service
      TypedRefCursorRowtypeService service = ExamplesRPCFactory.getTypedRefCursorRowtypeService();

      // call the stored procedure and receive a list as result
      List<RowtypeExample> rowtypeList = service.getRowtypeExample(ThreadLocalRandom.current().nextInt(10, 20), 0);

      // print information
      for (RowtypeExample product : rowtypeList) {
        System.out.format("n[%s]  f[%s]  ts[%s]  d[%s]  v[%s]%n", product.n, product.f, product.ts, product.d, product.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 : TypedRefCursorRowtypeSpringApi.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.TypedRefCursorRowtypeService;
import transferobject.RowtypeExample;

@Component
public class TypedRefCursorRowtypeSpringApi {
  @Autowired
  private TypedRefCursorRowtypeService typedRefCursorRowtypeService;

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

  private void runDemo() {
    try {
      // call the stored procedure and receive a list as result
      List<RowtypeExample> rowtypeList;
      rowtypeList = typedRefCursorRowtypeService.getRowtypeExample(ThreadLocalRandom.current().nextInt(10, 20), 0);

      // print information
      for (RowtypeExample product : rowtypeList) {
        System.out.format("n[%s]  f[%s]  ts[%s]  d[%s]  v[%s]%n",
                          product.getN(),
                          product.getF(),
                          product.getTs(),
                          product.getD(),
                          product.getV());
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

< Prev - Examples - Next >