Single Scalar Types

Example Description

• This simple example demonstrates a call of a stored procedure just returning a scalar value.
• The package has two stored procedure functions.
  • The package name SCALAR_TYPES is converted camel case to a Java service named ScalarTypesService
  • The functions ADD_NUM and CONCAT_CHAR are converted camel case to Java methods of the service named addNum and concatChar
• Both functions returns a scalar value so no transfer object is generated.
• 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.

Package Specification

PL/SQL Package Specification : SCALAR_TYPES

create or replace package scalar_types
/**
 * Demonstration of using simple scalar types.
 */
as

/**
 * Function adds two parameter numbers and returning the sum.
 *
 * @param i_n1 First parameter.
 * @param i_n2 Second parameter.
 * @return Sum of both parameter.
 */
function add_num(i_n1 in number, i_n2 in number) return number;

/**
 * Function concatenates two parameter varchar2 and returning the concatenated varchar2.
 *
 * @param i_s1 First parameter.
 * @param i_s2 Second parameter.
 * @return Concatenated parameters.
 */
function concat_char(i_s1 in varchar2, i_s2 in varchar2) return varchar2;
 
end scalar_types;

Package Body

PL/SQL Package Body : SCALAR_TYPES

create or replace package body scalar_types
as

function add_num(i_n1 in number, i_n2 in number) return number
is
begin
  return i_n1 + i_n2;
end add_num;

function concat_char(i_s1 in varchar2, i_s2 in varchar2) return varchar2
is
begin
  return i_s1 || i_s2;
end concat_char;

end scalar_types;

Factory API : Calling the PL/SQL package

Java Calling Class : ScalarTypesFactoryApi.java

package plsql_workbench_examples.factoryapi;

import factory.ExamplesRPCFactory;
import service.ScalarTypesService;

public class ScalarTypesFactoryApi {
  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
      ScalarTypesService service = ExamplesRPCFactory.getScalarTypesService();

      // call the stored procedure and prints the result
      int sum = service.addNum(1, 2);
      System.out.println("sum:" + sum);

      // call the stored procedure and prints the result
      String concat = service.concatChar("A", "B");
      System.out.println("concat:" + concat);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : ScalarTypesSpringApi.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.ScalarTypesService;

@Component
public class ScalarTypesSpringApi {
  @Autowired
  private ScalarTypesService scalarTypesService;

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

  private void runDemo()
  {
    try {
      // call the stored procedure and prints the result
      int sum = scalarTypesService.addNum(1, 2);
      System.out.println("sum:" + sum);

      // call the stored procedure and prints the result
      String concat = scalarTypesService.concatChar("A", "B");
      System.out.println("concat:" + concat);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}