Subclassed Objects

The object orientated programming in stored procedures is a little bit restricted. This example shows how to work with derived object type and how they are represented as Java transfer object classes.

Example Description

  • A base object is declared as PL/SQL object type named BASE_OBJECT with a member function val(). This PL/SQL object type is stored in the Java transfer object class BaseObject
  • An derived object is declared as PL/SQL object type named DERIVED_OBJECT overwriting the member function val(). This PL/SQL object type is stored in the Java transfer object class DerivedObject
  • The PL/SQL package has three functions :
    • Get a Java transfer object BaseObject of PL/SQL object type BASE_OBJECT.
    • Get a Java transfer object DerivedObject of PL/SQL object type DERIVED_OBJECT.
    • A function returning the type and the values of the PL/SQL object type for identification/inspection.
  • The Java calling class receives the transfer object when calling the stored procedure.
  • The transfer object is sent to the stored procedure and returns a string of the type and the values.

User Defined Object Specification of Parent Object

Object Type Specification : BASE_OBJECT
/**
 * Parent object containing one member function for inspect information.
 */
create or replace type base_object force as object (
  d           date,
  ts          timestamp,
  instance    varchar2(100),
  db_version  number(9),
  db_release  number(9),
  member function val return varchar2  
) not final;

User Defined Object Body of Parent Object

Object Type Body : BASE_OBJECT
create or replace type body base_object
as

member function val return varchar2
is
begin
  return 'd:'||to_char(d, 'yyyy/mm/dd')||'/ts:'||to_char(ts, 'yyyy/mm/dd hh:mi:ss')||'/instance:'||instance||'/db_version:'||db_version||'/db_release:'||db_release; 
end val;

end;

Generated Transfer Object Type from User Defined Object 'BASE_OBJECT'

Java Transfer Object : BaseObject.java
/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, Germany, Hamburg, 2015-2021. 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>BASE_OBJECT</em>.
 */
public class BaseObject implements Serializable {
  private static final long serialVersionUID = 1L;
  public Date d;
  public Timestamp ts;
  public String instance;
  public int dbVersion;
  public int dbRelease;
}

User Defined Object Specification of Subclassed Object

Object Type Specification : DERIVED_OBJECT
/**
 * Derived object containing overriding member function for inspect information.
 */
create or replace type derived_object force under base_object (
  new_s    varchar2(100),
  new_ts   timestamp,
  new_d    date,
  new_n    number(9),
  overriding member function val return varchar2
);

User Defined Object Body of Subclassed Object

Object Type Body : DERIVED_OBJECT
create or replace type body derived_object
as

overriding member function val return varchar2
is
begin
  return (self as base_object).val || '/new_s:'||new_s||'/new_ts:'||to_char(new_ts, 'yyyy/mm/dd hh:mi:ss')||'/new_d:'||to_char(new_d, 'yyyy/mm/dd')||'/new_n:'||new_n; 
end val;

end;

Generated Transfer Object Type from User Defined Object 'DERIVED_OBJECT'

Java Transfer Object : DerivedObject.java
/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, Germany, Hamburg, 2015-2021. 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.sql.Date;
import java.sql.Timestamp;

/**
 * Transfer object of user defined type <em>DERIVED_OBJECT</em>.
 */
final public class DerivedObject extends BaseObject {
  private static final long serialVersionUID = 1L;
  public String newS;
  public Timestamp newTs;
  public Date newD;
  public int newN;
}

PL/SQL Package Specification

PL/SQL Package Specification : SUBCLASSED_OBJECTS
create or replace package subclassed_objects
/**
 * 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 value.
 * @param o_col_varchar Varray(5)-Collection of varchar2 out put values.
 */
as

/**
 * Generating an object of type base_object.
 *
 * @return Object of type base_object.
 */
function get_base_object return base_object;

/**
 * Generating an object of type derived_object.
 *
 * @param i_diff Offset of field values of type derived_object.
 * @return Object of type derived_object.
 */
function get_derived_object(i_diff in number) return base_object;

/**
 * Calling member function 'val' of base_object or derived_object.
 *
 * @param base_object Object value to inspect.
 * @return Result of member function 'val'.
 */
function inspect_object(i_object in base_object) return varchar2;

end subclassed_objects;

PL/SQL Package Body

PL/SQL Package Body : SUBCLASSED_OBJECTS
create or replace package body subclassed_objects
as

function get_base_object return base_object
is
  o base_object;
begin
  o := base_object(null, null, null, null, null);
  o.d := sysdate;
  o.ts := systimestamp;
  select sys_context('USERENV','INSTANCE_NAME')
    into o.instance
    from dual;
  o.db_version := DBMS_DB_VERSION.VERSION;
  o.db_release := DBMS_DB_VERSION.RELEASE;
  return o;
end get_base_object;

function get_derived_object(i_diff in number) return base_object
is
  o base_object;
begin
  select derived_object(sysdate, 
                        systimestamp, 
                        sys_context('USERENV','INSTANCE_NAME'), 
                        DBMS_DB_VERSION.VERSION, 
                        DBMS_DB_VERSION.RELEASE, 
                        dbms_random.string('A', 100), 
                        systimestamp+i_diff, 
                        sysdate-i_diff, 
                        DBMS_RANDOM.value(0,999999999))
    into o
    from dual;  
  return o; 
end get_derived_object;

function inspect_object(i_object in base_object) return varchar2
is
  result varchar2(1000);
begin
  if (i_object is of(only base_object)) then
    result := 'base_object ['|| i_object.val ||']';
  elsif (i_object is of(only derived_object)) then
    result := 'derived_object ['|| i_object.val ||']';
  else 
    result := 'null or unknown object';
  end if;
  
  return result;
end inspect_object;

end subclassed_objects;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.
Java Calling Class : SubclassedObjectsFactoryApi.java
package plsql_workbench_examples.factoryapi;

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

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import factory.ExamplesRPCFactory;
import service.SubclassedObjectsService;
import transferobject.BaseObject;
import transferobject.DerivedObject;

public class SubclassedObjectsFactoryApi {
  public static void main(String[] args)
  {
    System.setProperty("baseURL", "http://localhost:8080");

    // Initialize factory class once - after that all services are accessible calling throw the static factory api.
    try (AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(ExamplesRPCFactory.class)) {
      runDemo();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void runDemo() throws Exception
  {
    int diff = 10;
    SubclassedObjectsService service = ExamplesRPCFactory.getSubclassedObjectsService();

    // create a base object from the database function
    BaseObject baseObject = service.getBaseObject();

    System.out.format("java-inspect: %s [d:%s/ts:%s/instance:%s/version:%d.%d]%n",
                      baseObject.getClass(),
                      baseObject.d,
                      baseObject.ts,
                      baseObject.instance,
                      baseObject.dbVersion,
                      baseObject.dbRelease);

    System.out.println("pl/sql inspect : " + service.inspectObject(baseObject));

    // create an derived object from the database function
    baseObject = service.getDerivedObject(diff);
    DerivedObject derivedObject = (DerivedObject) baseObject;
    System.out.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]%n",
                      baseObject.getClass(),
                      baseObject.d,
                      baseObject.ts,
                      baseObject.instance,
                      baseObject.dbVersion,
                      baseObject.dbRelease,
                      derivedObject.newS,
                      derivedObject.newTs,
                      derivedObject.newD,
                      derivedObject.newN);

    System.out.println("pl/sql inspect : " + service.inspectObject(baseObject));

    // inspect of object instantiated in java
    derivedObject = new DerivedObject();
    derivedObject.d = new Date(System.currentTimeMillis());
    derivedObject.instance = "any string";
    derivedObject.ts = new Timestamp(System.currentTimeMillis());
    derivedObject.dbVersion = 88;
    derivedObject.dbRelease = 99;
    derivedObject.newD = new Date(0);
    derivedObject.newN = 1234567890;
    derivedObject.newS = "another string";
    derivedObject.newTs = new Timestamp((long) (Math.random() * 1000L * 3600L * 24L * 365L * 50L));
    System.out.println("pl/sql inspect () : " + service.inspectObject(derivedObject));
  }
}

Spring API : Calling the PL/SQL package

Using Spring annotation to inject the service and call the remote service.
Java Calling Class : SubclassedObjectsSpringApi.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.annotation.AnnotationConfigApplicationContext;
import org.springframework.stereotype.Component;

import service.SubclassedObjectsService;
import transferobject.BaseObject;
import transferobject.DerivedObject;

@Component
public class SubclassedObjectsSpringApi {
  @Autowired
  private SubclassedObjectsService subclassedObjectsService;

  public static void main(String[] args)
  {
    System.setProperty("baseURL", "http://localhost:8080");
      
    // Register Spring Beans, Spring Context and call demo method 
    try (AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext("factory")) {
      ctx.register(SubclassedObjectsSpringApi.class);

      SubclassedObjectsSpringApi demo = ctx.getBean(SubclassedObjectsSpringApi.class);
      demo.runDemo();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

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

      // create a base object from the database function
      BaseObject baseObject = subclassedObjectsService.getBaseObject();

      System.out.format("java-inspect: %s [d:%s/ts:%s/instance:%s/version:%d.%d]%n",
                        baseObject.getClass(),
                        baseObject.d,
                        baseObject.ts,
                        baseObject.instance,
                        baseObject.dbVersion,
                        baseObject.dbRelease);

      System.out.println("pl/sql inspect : " + subclassedObjectsService.inspectObject(baseObject));

      // create an derived object from the database function
      baseObject = subclassedObjectsService.getDerivedObject(diff);
      DerivedObject derivedObject = (DerivedObject) baseObject;
      System.out.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]%n",
                        baseObject.getClass(),
                        baseObject.d,
                        baseObject.ts,
                        baseObject.instance,
                        baseObject.dbVersion,
                        baseObject.dbRelease,
                        derivedObject.newS,
                        derivedObject.newTs,
                        derivedObject.newD,
                        derivedObject.newN);

      System.out.println("pl/sql inspect : " + subclassedObjectsService.inspectObject(baseObject));

      // inspect of object instantiated in java
      derivedObject = new DerivedObject();
      derivedObject.d = new Date(System.currentTimeMillis());
      derivedObject.instance = "any string";
      derivedObject.ts = new Timestamp(System.currentTimeMillis());
      derivedObject.dbVersion = 88;
      derivedObject.dbRelease = 99;
      derivedObject.newD = new Date(0);
      derivedObject.newN = 1234567890;
      derivedObject.newS = "another string";
      derivedObject.newTs = new Timestamp((long) (Math.random() * 1000L * 3600L * 24L * 365L * 50L));
      System.out.println("pl/sql inspect () : " + subclassedObjectsService.inspectObject(derivedObject));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}