< Prev - Examples - Next >

Simple Scalar Types

Use Case

Update data of a single row.

Example Description

Package Specification

The package 'SCALAR_TYPES' defines two functions.
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

The package body 'SCALAR_TYPES' implements two functions.
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

Using the static factory to get the remote service.
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 {
      // 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

Using Spring annotation to inject the service and call the remote service.
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) {
    // 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();
    }
  }
}