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
- A simple PL/SQL package with one stored procedure.
- Three collection types are declared inside the package specification.
- The stored procedure has three parameter of collections. A IN-parameter, a IN/OUT-parameter and a OUT-parameter.
- The stored procedure is called from the service with two Java list arguments and receives a Java transfer object with two Java lists as attributes.
- In real world scenarios you will access tables in your database.
Important Notes !
- The JDBC call of in-package defined collections is only supported under Oracle 12c - it is not supported under Oracle 11g
- Allowed collections in-package defined scalar element types are : NUMBERS, VARCHARS, TIMESTAMP, DATE, XMLTYPE, BLOB, CLOB, SDO_GEOMETRY.
- Unsupported collection element types are : BFILE, PL/SQL boolean, PL/SQL table, PL/SQL record
Package Specification
The package 'COLLECTION_IN_PACKAGE' defines a procedure working with collections as input and output parameter.PL/SQL Package Specification :
COLLECTION_IN_PACKAGE

create or replace package collection_in_package
as
/**
* Demonstration of calling in-package defined collections.
*/
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);
/**
* Demonstration of calling a stored procedure with in/put parameter of collections of scalar element types.
*
* @param i_col_number Table-Collection of integer input values.
* @param io_col_timestamp Table-Collection of timestamp input/output values.
* @param o_col_varchar Varray(5)-Collection of varchar2 output values.
*/
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;
Package Body
The package 'COLLECTION_IN_PACKAGE' implements a procedure working with collections as input and output parameter.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;
Factory API : Calling the PL/SQL package
Using the static factory to get the remote service.Java Calling Class :
CollectionsOfScalarInPackageTypesFactoryApi.java

package plsql_workbench_examples.factoryapi;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import factory.ExamplesRPCFactory;
import service.CollectionInPackageService;
import transferobject.CollectionInPackageTO;
public class CollectionsOfScalarInPackageTypesFactoryApi {
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
CollectionInPackageService service = ExamplesRPCFactory.getCollectionInPackageService();
// make some test data
List<Integer> numberList = new ArrayList<>();
List<Timestamp> timestampList = new ArrayList<>();
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();
}
}
}
Spring API : Calling the PL/SQL package
Using Spring annotation to inject the service and call the remote service.Java Calling Class :
CollectionsOfScalarInPackageTypesSpringApi.java

package plsql_workbench_examples.springapi;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;
import service.CollectionInPackageService;
import transferobject.CollectionInPackageTO;
@Component
public class CollectionsOfScalarInPackageTypesSpringApi {
@Autowired
private CollectionInPackageService collectionInPackageService;
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(CollectionsOfScalarInPackageTypesSpringApi.class)) {
ctx.getBean(CollectionsOfScalarInPackageTypesSpringApi.class).runDemo();
}
}
private void runDemo()
{
try {
// 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 = collectionInPackageService.doIt(numberList, timestampList);
// print result data
for (Timestamp ts : result.getIoColTimestamp()) {
System.out.println("ts:" + ts);
}
for (String s : result.getOColVarchar()) {
System.out.println(s);
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}