< Prev - Examples - Next >

PL/SQL Table

Use Case

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

Example Description

Important Notes !

PL/SQL Package Specification : PLSQL_TABLE
create or replace package plsql_table
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;

-- only in-parameter are supported by the generator 
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;
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;
Java Calling Class : PlSqlTable.java
package plsql_workbench_examples;

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

public class PlSqlTable {
  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();
    }
  }
}