Simple Object

Example Description

• A object type specification and body is created with a new constructor.
• Each object type will result in a Java transfer object class using the camel case naming conversion for the Java class and attributes.
• The stored procedure function instantiate an object type by calling the new constructor and returns this instance.
• The Java calling code receives the transfer object when calling the stored procedure.

Important Notes !

• Objects types will be represented as a Java transfer object class.
• Objects are supported to use as stored procedure parameter by the JDBC driver in opposition to PL/SQL records.
• Objects can be used as parameter, column types, be derived and in collections as type TABLE and VARRAY.
• All supported types for fields/attributes are : NUMBERS, VARCHARS, TIMESTAMP, DATE, XMLTYPE, SDO_GEOMETRY, object types, derived object typed and collections of object types.
• Unsupported types for fields/attributes are : BFILE, PL/SQL boolean, PL/SQL table, PL/SQL record

User Defined Object Specification

Object Type Specification : SIMPLE_OBJECT

create or replace type simple_object force as object (
  d           date,
  ts          timestamp,
  instance    varchar2(100),
  db_version  number(9),
  db_release  number(9),
  constructor function simple_object(i_diff number) return self as result  
);

User Defined Object Body

Object Type Specification : SIMPLE_OBJECT

create or replace type body simple_object
as
  constructor function simple_object(i_diff number)
    return self as result
  as
  begin
    self.d := sysdate + i_diff;
    self.ts := systimestamp - i_diff;
    select sys_context('USERENV','INSTANCE_NAME')
      into self.instance
      from dual;
    self.db_version := DBMS_DB_VERSION.VERSION;
    self.db_release := DBMS_DB_VERSION.RELEASE;
    return;
  end;
end;

Generated Transfer Object Type from User Defined Object

Java Transfer Object : SimpleObject.java

/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, www.jr-database-tools.com, Switzerland, 2015-2024. All rights reserved.
 *
 * THIS FILE IS NOT INTENDED TO BE MODIFIED - IT WILL BE OVERWRITTEN ON EVERY RUN OF THE CONNECTOR BUILDER
 */
package transferobject;

import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

/**
 * Transfer object of user defined type <em>SIMPLE_OBJECT</em>.
 */
final public class SimpleObject implements Serializable {
  private static final long serialVersionUID = 1L;
  public Date d;
  public Timestamp ts;
  public String instance;
  public int dbVersion;
  public int dbRelease;
}

PL/SQL Procedure

PL/SQL Procedure : SERVER_INFO_SIMPLE_OBJECT

create or replace function server_info_simple_object
(
  i_diff 			in	 	number
)
return simple_object
is
begin
  return simple_object(i_diff);
end server_info_simple_object;

Factory API : Calling the PL/SQL package

Java Calling Class : ServerInfoSimpleObjectFactoryApi.java

package plsql_workbench_examples.factoryapi;

import factory.ExamplesRPCFactory;
import service.ServerInfoSimpleObjectService;
import transferobject.SimpleObject;

public class ServerInfoSimpleObjectFactoryApi {
  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");

      int diff = 10;

      // get the service
      ServerInfoSimpleObjectService service = ExamplesRPCFactory.getServerInfoSimpleObjectService();

      // calling the stored procedure, receiving a transfer object
      SimpleObject info = service.call(diff);

      // print server information
      System.out.println("database date(+" + diff + "):" + info.d);
      System.out.println("database timestamp(-" + diff + "):" + info.ts);
      System.out.println("database instance name:" + info.instance);
      System.out.println("database version:" + info.dbVersion + "." + info.dbRelease);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : ServerInfoSimpleObjectSpringApi.java

package plsql_workbench_examples.springapi;

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

import service.ServerInfoSimpleObjectService;
import transferobject.SimpleObject;

@Component
public class ServerInfoSimpleObjectSpringApi {
  @Autowired
  private ServerInfoSimpleObjectService ServerInfoSimpleObjectService;

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

  private void runDemo()
  {
    try {
      int diff = 10;

      // calling the stored procedure, receiving a transfer object
      SimpleObject info = ServerInfoSimpleObjectService.call(diff);

      // print server information
      System.out.println("database date(+" + diff + "):" + info.getD());
      System.out.println("database timestamp(-" + diff + "):" + info.getTs());
      System.out.println("database instance name:" + info.getInstance());
      System.out.println("database version:" + info.getDbVersion() + "." + info.getDbRelease());
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}