< Prev - Examples - Next >

Collections Of Scalar Types - In Package Definition

This example is a demonstration of using collections defined inside the package specification. The next example demonstrate the same functionality of collections defined as user type.

Example Description

Important Notes !

PL/SQL Package Specification : COLLECTION_IN_PACKAGE
create or replace package collection_in_package
as

type number_table_in_package is table of number(9);
type timestamp_table_in_package is table of timestamp;
type varchar_varray_in_package is varray(5) of varchar2(100);

procedure do_it
(
  i_col_number      in     number_table_in_package,
  io_col_timestamp  in out timestamp_table_in_package,
  o_col_varchar     out    varchar_varray_in_package
);

end collection_in_package;
PL/SQL Package Body : COLLECTION_IN_PACKAGE
create or replace package body collection_in_package
as

procedure do_it
(
  i_col_number      in     number_table_in_package,
  io_col_timestamp  in out timestamp_table_in_package,
  o_col_varchar     out    varchar_varray_in_package
)
is
begin
  for i in 1..greatest(io_col_timestamp.count, i_col_number.count) loop
    if (i <= io_col_timestamp.count) then
      if (i <= i_col_number.count) then
        -- add <n> days to timestamp of number list 
        io_col_timestamp(i) := io_col_timestamp(i) + i_col_number(i);
      else 
        -- add <n> days to timestamp from random value
        io_col_timestamp(i) := io_col_timestamp(i) + dbms_random.value(-3000,3000);
      end if;
    else
      -- add <n> days of current database systimestamp
      io_col_timestamp.extend();
      io_col_timestamp(i) := systimestamp + i_col_number(i);
    end if;
  end loop;
  
  -- fill string collection with formatted timestamp values
  o_col_varchar := varchar_varray_in_package();
  for i in 1..least(io_col_timestamp.count, o_col_varchar.limit) loop
    o_col_varchar.extend();
    o_col_varchar(i) := to_char(io_col_timestamp(i));
  end loop;
end do_it;

end collection_in_package;
Java Calling Class : CollectionsOfScalarInPackageTypes.java
package plsql_workbench_examples;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import factory.ExamplesRPCFactory;
import service.CollectionInPackageService;
import transferobject.CollectionInPackageTO;

public class CollectionsOfScalarInPackageTypes {
  public static void main(String[] args) {
    try {
      // get the service
      CollectionInPackageService service = ExamplesRPCFactory.getCollectionInPackageService();

      // make some test data
      List<Integer> numberList = new ArrayList<Integer>();
      List<Timestamp> timestampList = new ArrayList<Timestamp>();

      for (int i = 0; i < 8; i++) {
        numberList.add(i);
      }

      for (int i = 0; i < 6; i++) {
        timestampList.add(new Timestamp(System.currentTimeMillis()));
      }

      // call the service
      CollectionInPackageTO.DoItTO result = service.doIt(numberList, timestampList);

      // print result data
      for (Timestamp ts : result.ioColTimestamp) {
        System.out.println("ts:" + ts);
      }
      for (String s : result.oColVarchar) {
        System.out.println(s);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}