< Prev - Examples - Next >

PL/SQL Table

Use Case

To insert or update a list of rows. Bulk processing. Dynamic list of predicates.

Example Description

Important Notes !

Package Specification

The package 'PLSQL_TABLE' defines a procedure with two input parameter of PL/SQL tables.
PL/SQL Package Specification : PLSQL_TABLE
create or replace package plsql_table
/**
 * Demonstration of PL/SQL table types.
 */
as

-- only Pl/Sql tables of number and varchar2 are supported by the the jdbc driver
type t_number_plsql_table is table of number(9) index by binary_integer;
type t_varchar2_plsql_table is table of varchar2(100) index by binary_integer;


/**
 * Demo summarizes all elements of i_number_table and count all characters in varchar2 PL/SQL table.
 *
 * @param i_number_table PL/SQL table of integers.
 * @param i_varchar2_table PL/SQL table of varchar2.
 * @param o_number_table_sum Sum of all integers.
 * @param o_varchar2_table_length Sum of length of all varchar2 elements.
 */
procedure doit
(
  i_number_table          in  t_number_plsql_table,
  i_varchar2_table        in  t_varchar2_plsql_table,
  o_number_table_sum      out number,
  o_varchar2_table_length out number
);

end plsql_table;

Package Body

The package 'PLSQL_TABLE' implements a procedure with two input parameter of PL/SQL tables.
PL/SQL Package Body : PLSQL_TABLE
create or replace package body plsql_table
as

procedure doit
(
  i_number_table          in  t_number_plsql_table,
  i_varchar2_table        in  t_varchar2_plsql_table,
  o_number_table_sum      out number,
  o_varchar2_table_length out number
)
is
  
begin
  o_number_table_sum := 0;
  for i in 1..i_number_table.count loop
    o_number_table_sum := o_number_table_sum + i_number_table(i);
  end loop;
  
  o_varchar2_table_length := i_varchar2_table.count;
end doit;

end plsql_table;

Factory API : Calling the PL/SQL package

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

import factory.ExamplesRPCFactory;
import service.PlsqlTableService;
import transferobject.PlsqlTableTO;

public class PlSqlTableFactoryApi {
  public static void main(String[] args) {
    try {
      // get the service
      PlsqlTableService service = ExamplesRPCFactory.getPlsqlTableService();

      // creating some test data
      Integer[] numberTable = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
      String[] varchar2Table = { "A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12" };

      // call the stored procedure
      PlsqlTableTO.DoitTO result = service.doit(numberTable, varchar2Table);

      // print the sum off all numbers and the length of the string-array
      System.out.println("sum:" + result.oNumberTableSum);
      System.out.println("count lines:" + result.oVarchar2TableLength);
    }
    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 : PlSqlTableSpringApi.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.PlsqlTableService;
import transferobject.PlsqlTableTO;

@Component
public class PlSqlTableSpringApi {
  @Autowired
  private PlsqlTableService plsqlTableService;

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

  private void runDemo() {
    try {
      // creating some test data
      Integer[] numberTable = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
      String[] varchar2Table = { "A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12" };

      // call the stored procedure
      PlsqlTableTO.DoitTO result = plsqlTableService.doit(numberTable, varchar2Table);

      // print the sum off all numbers and the length of the string-array
      System.out.println("sum:" + result.getONumberTableSum());
      System.out.println("count lines:" + result.getOVarchar2TableLength());
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}