Collections of PL/SQL Record
Example Description
- Using a PL/SQL Record as collection element.
PL/SQL Package Specification
PL/SQL Package Specification :
COLLECTIONS_OF_PLSQL_RECORDS

create or replace package collection_of_plsql_records
as
/**
* Demonstration of calling in-package defined collections of element type PL/SQL Record.
*/
type plsql_record is record (
d date,
ts timestamp,
s varchar2(100)
);
type table_plsql_record is table of plsql_record;
/**
* Demonstration of calling a stored procedure with input of table of PL/SQL record elements. The elements are modified and returned as output parameter.
*
* @param i_delta_d Offset is added to date value.
* @param i_delta_ts Offset is added to timestamp value.
* @param i_table_plsql_record Input list of collection PL/SQL Records.
* @param o_table_plsql_record Output list of collection PL/SQL Records.
*/
procedure doit
(
i_delta_d in number,
i_delta_ts in number,
i_table_plsql_record in table_plsql_record,
o_table_plsql_record out table_plsql_record
);
end collection_of_plsql_records;
PL/SQL Package Implementation
PL/SQL Package Body :
COLLECTIONS_OF_PLSQL_RECORDS

create or replace package body collection_of_plsql_records
as
/**
* Demonstration of calling a stored procedure with input of table of PL/SQL record elements. The elements are modified and returned as output parameter.
*
* @param i_delta_d Offset is added to date value.
* @param i_delta_ts Offset is added to timestamp value.
* @param i_table_plsql_record Input list of collection PL/SQL Records.
* @param o_table_plsql_record Output list of collection PL/SQL Records.
*/
procedure doit
(
i_delta_d in number,
i_delta_ts in number,
i_table_plsql_record in table_plsql_record,
o_table_plsql_record out table_plsql_record
)
is
begin
o_table_plsql_record := table_plsql_record();
-- append elements to collection
for i in 1..i_table_plsql_record.count loop
o_table_plsql_record.extend();
o_table_plsql_record(i).d := i_table_plsql_record(i).d + i_delta_d + i;
o_table_plsql_record(i).ts := i_table_plsql_record(i).ts + i_delta_ts + i;
o_table_plsql_record(i).s := i_table_plsql_record(i).s || ' - ' || DBMS_RANDOM.string('A',10);
end loop;
end doit;
end collection_of_plsql_records;
Factory API : Calling the PL/SQL package
Using the static factory to get the remote service.Java Calling Class :
CollectionsOfPlSqlRecordsFactoryApi.java

package plsql_workbench_examples.factoryapi;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import factory.ExamplesRPCFactory;
import service.CollectionOfPlsqlRecordsService;
import transferobject.CollectionOfPlsqlRecordsTO;
import transferobject.CollectionOfPlsqlRecordsTO.PlsqlRecord;
public class CollectionsOfPlSqlRecordsFactoryApi {
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
CollectionOfPlsqlRecordsService service = ExamplesRPCFactory.getCollectionOfPlsqlRecordsService();
// create some test data
List<CollectionOfPlsqlRecordsTO.PlsqlRecord> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
CollectionOfPlsqlRecordsTO.PlsqlRecord o = new CollectionOfPlsqlRecordsTO.PlsqlRecord();
o.d = new Date(System.currentTimeMillis());
o.s = "init value";
o.ts = new Timestamp(System.currentTimeMillis());
list.add(o);
}
// call the service and receive result list of transfer objects
List<CollectionOfPlsqlRecordsTO.PlsqlRecord> resultList = service.doit(3, 7, list);
// printing the result list
for (PlsqlRecord record : resultList) {
System.out.format("d[%s] ts[%s] s[%s]%n", record.d, record.ts, record.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 :
CollectionsOfPlSqlRecordsSpringApi.java

package plsql_workbench_examples.springapi;
import java.sql.Date;
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.CollectionOfPlsqlRecordsService;
import transferobject.CollectionOfPlsqlRecordsTO;
import transferobject.CollectionOfPlsqlRecordsTO.PlsqlRecord;
@Component
public class CollectionsOfPlSqlRecordsSpringApi {
@Autowired
private CollectionOfPlsqlRecordsService collectionOfPlsqlRecordsService;
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(CollectionsOfPlSqlRecordsSpringApi.class)) {
ctx.getBean(CollectionsOfPlSqlRecordsSpringApi.class).runDemo();
}
}
public void runDemo()
{
try {
// create some test data
List<CollectionOfPlsqlRecordsTO.PlsqlRecord> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
CollectionOfPlsqlRecordsTO.PlsqlRecord o = new CollectionOfPlsqlRecordsTO.PlsqlRecord();
o.setD(new Date(System.currentTimeMillis()));
o.setS("init value");
o.setTs(new Timestamp(System.currentTimeMillis()));
list.add(o);
}
// call the service and receive result list of transfer objects
List<CollectionOfPlsqlRecordsTO.PlsqlRecord> resultList = collectionOfPlsqlRecordsService.doit(3, 7, list);
// printing the result list
for (PlsqlRecord record : resultList) {
System.out.format("d[%s] ts[%s] s[%s]%n", record.getD(), record.getTs(), record.getS());
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}