PL/SQL Table

Use Case

Example Description

• The types of two PL/SQL tables are declared in the package specification.
• The stored procedure just add all numbers and returns the sum and the length of the varchar2-table.

Important Notes !

• Only two types of PL/SQL tables are qualified to work with the JDBC driver :
  • Tables of VARCHAR2 : type t_number_plsql_table is table of number(9) index by binary_integer;
  • Tables of NUMBER : type t_varchar2_plsql_table is table of varchar2(100) index by binary_integer;
    The numeric Java type depends on the numeric conversion model the Connector Builder is configured.
• Understand the difference between PL/SQL table and collections :
  • Collection declaration : type t_number_plsql_table is table of number(9);
    In Oracle 11c collections must be declared outside a package as user type (required to work with the JDBC driver). Oracle 12g does not have this limitation.
  • PL/SQL table declaration : type t_number_plsql_table is table of number(9) index by binary_integer;
    PL/SQL tables are declared inside the package specification.
• The Connector Builder supports PL/SQL tables as output parameter, but you have to reserve a buffer size before the stored procedure call. If the buffer is to small, the execution is aborted with failure without receiving any data.
• It is highly recommend to use a typed reference cursor to get a list of output values.

Package Specification

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

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

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 {
      // 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
      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

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)
  {
    // 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(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();
    }
  }
}