Scalar Types Returning A Transfer Object

Example Description

• The stored procedure procedure has one input and five out parameter.
• A Java transfer object is generated to store the return values.
• The procedure is called with one parameter and returns some database information values.
  • The procedure name SERVER_INFO is converted camel case to a Java service named ServerInfoService
  • A Java transfer object ServerInfoTO is generated for the return values.
  • The Java transfer object class attributes are named camel case like the stored procedure parameter.
• The Java code based on the Java RPC Connector Builder just gets the service and calls the procedure.
  • The factory has a static method getServerInfoService() returning the service.
  • The service has a methods call(..) to call the stored procedure and return the values of type ServerInfoTO.

Stored Procedure

PL/SQL Procedure : SERVER_INFO

create or replace procedure server_info
(
  i_diff        in   number,
  o_date        out  date,
  o_timestamp   out  timestamp,
  o_instance    out  varchar2,
  o_db_version  out  number,
  o_db_release  out  number
)
/**
 * Database server information.
 *
 * @param i_diff Offset of date and timestamp value.
 * @param o_date System date plus i_diff offset.
 * @param o_timestamp System timestamp plus i_diff offset.
 * @param o_instance Instance information.
 * @param o_db_version Oracle version.
 * @param o_db_release Oracle release.
 */
is
begin
  o_date := sysdate + i_diff;
  o_timestamp := systimestamp - i_diff;
  select sys_context('USERENV','INSTANCE_NAME')
    into o_instance
    from dual;
  o_db_version := DBMS_DB_VERSION.VERSION;
  o_db_release := DBMS_DB_VERSION.RELEASE;
end server_info;

Generated Transfer Object

Java Transfer Object : ServerInfoTO.java

/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, www.jr-database-tools.com, Switzerland, 2015-2024. All rights reserved.
 *
 * THIS FILE IS NOT INTENDED TO BE MODIFIED - IT WILL BE OVERWRITTEN ON EVERY RUN OF THE CONNECTOR BUILDER
 */
package transferobject;

import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

/**
 * Transfer object returned from PL/SQL procedure <em>SERVER_INFO</em>.<p>
 */
final public class ServerInfoTO implements Serializable {
  private static final long serialVersionUID = 1L;

  /** System date plus iDiff offset. */ 
  public Date oDate;

  /** System timestamp plus iDiff offset. */ 
  public Timestamp oTimestamp;

  /** Instance information. */ 
  public String oInstance;

  /** Oracle version. */ 
  public int oDbVersion;

  /** Oracle release. */ 
  public int oDbRelease;
}

Factory API : Calling the PL/SQL procedure

Java Calling Class : ServerInfoFactoryApi.java

package plsql_workbench_examples.factoryapi;

import factory.ExamplesRPCFactory;
import service.ServerInfoService;
import transferobject.ServerInfoTO;

public class ServerInfoFactoryApi {
  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");

      int diff = 10;

      // get the service
      ServerInfoService service = ExamplesRPCFactory.getServerInfoService();

      // calling the stored procedure, receiving a transfer object
      ServerInfoTO info = service.call(diff);

      // print server information
      System.out.println("database date(+" + diff + "):" + info.oDate);
      System.out.println("database timestamp(-" + diff + "):" + info.oTimestamp);
      System.out.println("database instance name:" + info.oInstance);
      System.out.println("database version:" + info.oDbVersion + "." + info.oDbRelease);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL procedure

Java Calling Class : ServerInfoSpringApi.java

package plsql_workbench_examples.springapi;

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

import service.ServerInfoService;
import transferobject.ServerInfoTO;

@Component
public class ServerInfoSpringApi {
  @Autowired
  private ServerInfoService serverInfoService;

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

  private void runDemo()
  {
    try {
      int diff = 10;

      // calling the stored procedure, receiving a transfer object
      ServerInfoTO info = serverInfoService.call(diff);

      // print server information
      System.out.println("database date(+" + diff + "):" + info.getODate());
      System.out.println("database timestamp(-" + diff + "):" + info.getOTimestamp());
      System.out.println("database instance name:" + info.getOInstance());
      System.out.println("database version:" + info.getODbVersion() + "." + info.getODbRelease());
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}