< Prev - Examples - Next >

PL/SQL Record

Example Description

Important Notes !

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;
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;
Java Calling Class : PlSqlRecord.java
package plsql_workbench_examples;

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

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

public class PlSqlRecord {
  public static void main(String[] args) {
    try {
      // 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();
    }
  }
}