PL/SQL Record

Example Description

• Simple PL/SQL record demonstration as input and output value.

Important Notes !

• Only Oracle 12c (and higher) is supported.

Package Specification

PL/SQL Package Specification : PLSQL_RECORD

create or replace package plsql_record
as
/**
 * PL/SQL record Demo.
 */

-- this type of PL/SQL record will create a transfer object
type demo_record is record (
  d         date,
  ts        timestamp,
  n         number(9),
  f         number(12,3),
  s         varchar2(100)
);

/**
 * Input and output parameter of type PL/SQL record.
 *
 * @param io_rec Input parameter record value is modified and returned.
 */
procedure modify_record(io_rec in out demo_record);

end plsql_record;

Package Body

PL/SQL Package Body : PLSQL_RECORD

create or replace package body plsql_record
as

procedure modify_record(io_rec in out demo_record)
is
begin
  io_rec.d  := io_rec.d + 1;
  io_rec.ts := io_rec.ts + 1;
  io_rec.n  := io_rec.n + 1;
  io_rec.f  := io_rec.f + 1;
  io_rec.s  := io_rec.s || '1';
end modify_record;

end plsql_record;

Factory API : Calling the PL/SQL package

Java Calling Class : PlSqlRecordFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.sql.Date;
import java.sql.Timestamp;

import factory.ExamplesRPCFactory;
import service.PlsqlRecordService;
import transferobject.PlsqlRecordTO;

public class PlSqlRecordFactoryApi {
  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
      PlsqlRecordService service = ExamplesRPCFactory.getPlsqlRecordService();

      // creating some test data
      PlsqlRecordTO.DemoRecord record = new PlsqlRecordTO.DemoRecord();
      record.d = new Date(System.currentTimeMillis());
      record.f = 3.14;
      record.n = 4711;
      record.s = "some kind of string";
      record.ts = new Timestamp(System.currentTimeMillis());

      // call the stored procedure
      record = service.modifyRecord(record);

      // print record field values
      System.out.format("d:%1$tD   f:%2$f   n:%3$d   s:%4$s   ts:%5$tD %5$tT",
                        record.d,
                        record.f,
                        record.n,
                        record.s,
                        record.ts);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : PlSqlRecordSpringApi.java

package plsql_workbench_examples.springapi;

import java.sql.Date;
import java.sql.Timestamp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;

import service.PlsqlRecordService;
import transferobject.PlsqlRecordTO;

@Component
public class PlSqlRecordSpringApi {
  @Autowired
  private PlsqlRecordService plsqlRecordService;

  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(PlSqlRecordSpringApi.class)) {
      ctx.getBean(PlSqlRecordSpringApi.class).runDemo();
    }
  }

  private void runDemo()
  {
    try {
      // creating some test data
      PlsqlRecordTO.DemoRecord record = new PlsqlRecordTO.DemoRecord();
      record.setD(new Date(System.currentTimeMillis()));
      record.setF(3.14);
      record.setN(4711);
      record.setS("some kind of string");
      record.setTs(new Timestamp(System.currentTimeMillis()));

      // call the stored procedure
      record = plsqlRecordService.modifyRecord(record);

      // print record field values
      System.out.format("d:%1$tD   f:%2$f   n:%3$d   s:%4$s   ts:%5$tD %5$tT",
                        record.getD(),
                        record.getF(),
                        record.getN(),
                        record.getS(),
                        record.getTs());
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}