SQL records

A StoredProcedure in a PL/SQL package uses a PL/SQL record type for arguments:

  CREATE OR REPLACE PACKAGE NEST_REC_PACKAGE AS
    TYPE TEST_NEST_REC IS RECORD (
      VALUE01 VARCHAR2(10),
      VALUE02 NUMBER
    );
    TYPE TEST_REC IS RECORD (
      COLUMN01 VARCHAR2(10),
      COLUMN02 NUMBER,
      NEST01 TEST_NEST_REC 
    );
    PROCEDURE PROCEDURE_RECORD_CALL_NEST(
      INPUT IN TEST_REC,
      OUTPUT OUT TEST_REC
    );
  END NEST_REC_PACKAGE;

First, we need some model classes for the PL/SQL record types - very simple:
{for demo purposes, I put everything in the package 'test' but that can be changed easily ...}

package test;

import java.math.BigDecimal;

public class NestedRecord {

    public String value01;
    public BigDecimal value02;
    
    public NestedRecord() {
    }
}
...
public class Record {
    
    public String column01;
    public BigDecimal column02;
    public NestedRecord nest01;

    public Record() {
    }
}

A simple test class shows mappings for each type as well as how to build a query to execute the stored procedure.

Some of the key points:

 - complex types that are defined in a PL/SQL package (records, collections or any combination thereof)
   require 'shadow' types that are defined in the 'global' JDBC namespace outside of any PL/SQL package.
  Thus for the record "TEST_REC", we need the shadow type "NEST_REC_PACKAGE_TEST_REC". The
  shadow type must have the same 'shape' - same number of fields, in the same order, with the same names.
  (Note the simple transform from the PL/SQL world to the JDBC world: package name + "_" + record name).

 - in the case of the stored procedure "PROCEDURE_RECORD_CALL_NEST", the record record "TEST_REC" is used
   twice, once for the IN parameter "INPUT" and second for the OUT parameter "OUTPUT". However, EclipseLink
  needs separate instances of the PLSQLrecord object

{other notes: all Oracle database artifacts - type names, package names, etc. should always be represented in
EclipseLink metadata in UPPERCASE}

...
//javase imports
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

//EclipseLink imports
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.mappings.DirectToFieldMapping;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
import org.eclipse.persistence.mappings.structures.StructureMapping;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
import org.eclipse.persistence.queries.ValueReadQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.DatasourceLogin;
import org.eclipse.persistence.sessions.Project;

public class NestedRecordTest {

    static String username;
    static String password;
    static String url; 
    static final String DATABASE_USERNAME_KEY = "db.user";
    static final String DATABASE_PASSWORD_KEY = "db.pwd";
    static final String DATABASE_URL_KEY = "db.url";
    static final String DEFAULT_DATABASE_USERNAME = "scott";
    static final String DEFAULT_DATABASE_PASSWORD = "tiger";
    static final String DEFAULT_DATABASE_DRIVER = "oracle.jdbc.OracleDriver";
    static final String DEFAULT_DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
    static final String PACKAGE_NAME = "NEST_REC_PACKAGE";
    static final String PROCEDURE_NAME = "PROCEDURE_RECORD_CALL_NEST";
    static final String NEST_RECORD_TYPE_NAME = "TEST_NEST_REC";
    static final String RECORD_TYPE_NAME = "TEST_REC";
    static final String CREATE_PACKAGE =
        "CREATE OR REPLACE PACKAGE " + PACKAGE_NAME + " AS\n" +
            "TYPE " + NEST_RECORD_TYPE_NAME + " IS RECORD (\n" +
              "VALUE01 VARCHAR2(10),\n" +
              "VALUE02 NUMBER\n" +
            ");\n" +
            "TYPE " + RECORD_TYPE_NAME + " IS RECORD (\n" +
              "COLUMN01 VARCHAR2(10),\n" +
              "COLUMN02 NUMBER,\n" +
              "NEST01 " + NEST_RECORD_TYPE_NAME + " \n" +
            ");\n" +
            "PROCEDURE " + PROCEDURE_NAME + "(\n" +
              "INPUT IN " + RECORD_TYPE_NAME + ",\n" +
              "OUTPUT OUT " + RECORD_TYPE_NAME + "\n" +
            ");\n" +
        "END " + PACKAGE_NAME + ";";
    static final String CREATE_BODY =
        "CREATE OR REPLACE PACKAGE BODY " + PACKAGE_NAME + " AS\n" +
          "PROCEDURE " + PROCEDURE_NAME + "(\n" +
            "INPUT IN " + RECORD_TYPE_NAME + ",\n" +
            "OUTPUT OUT " + RECORD_TYPE_NAME + ") AS\n" +
          "BEGIN\n" +
            "NULL;\n" +
          "END " + PROCEDURE_NAME + ";\n" +
        "END " + PACKAGE_NAME + ";";
    static final String JDBC_SHADOW_TYPE1 = 
        "NEST_REC_PACKAGE_TEST_NEST_REC";
    static final String JDBC_SHADOW_TYPE2 = 
        "NEST_REC_PACKAGE_TEST_REC";
    static final String CREATE_JDBC_SHADOW_TYPE1 =
        "CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE1 + " AS OBJECT (\n" +
            "VALUE01 VARCHAR2(10),\n" +
            "VALUE02 NUMBER\n" +
        ")\n";
    static final String CREATE_JDBC_SHADOW_TYPE2 =
        "CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE2 + " AS OBJECT (\n" +
            "COLUMN01 VARCHAR2(10),\n" +
            "COLUMN02 NUMBER,\n" +
            "NEST01 " + JDBC_SHADOW_TYPE1 + " \n" +
        ")\n";
    static final String DROP_PACKAGE =
        "DROP PACKAGE NEST_REC_PACKAGE";
    static final String DROP_JDBC_SHADOW_TYPE1 =
        "DROP TYPE " + JDBC_SHADOW_TYPE1;
    static final String DROP_JDBC_SHADOW_TYPE2 =
        "DROP TYPE " + JDBC_SHADOW_TYPE2;
    
    static DatabaseSession ds = null;
    
    public static void main(String...args) {
        username = System.getProperty(DATABASE_USERNAME_KEY, DEFAULT_DATABASE_USERNAME);
        password = System.getProperty(DATABASE_PASSWORD_KEY, DEFAULT_DATABASE_PASSWORD);
        url = System.getProperty(DATABASE_URL_KEY, DEFAULT_DATABASE_URL); 
        
        DatasourceLogin login = new DatabaseLogin();
        login.setUserName(username);
        login.setPassword(password);
        ((DatabaseLogin)login).setConnectionString(url);
        ((DatabaseLogin)login).setDriverClassName(DEFAULT_DATABASE_DRIVER);
        login.setDatasourcePlatform(new Oracle11Platform());
        ((DatabaseLogin)login).bindAllParameters();
        
        Project p = new Project(login);
        ObjectRelationalDataTypeDescriptor recordDescriptor = new ObjectRelationalDataTypeDescriptor();
        recordDescriptor.descriptorIsAggregate();
        recordDescriptor.setJavaClass(test.Record.class);
        recordDescriptor.setAlias("Record");
        recordDescriptor.setStructureName(JDBC_SHADOW_TYPE2);
        DirectToFieldMapping column01Mapping = new DirectToFieldMapping();
        column01Mapping.setAttributeName("column01");
        column01Mapping.setFieldName("COLUMN01");
        recordDescriptor.addMapping(column01Mapping);
        DirectToFieldMapping column02Mapping = new DirectToFieldMapping();
        column02Mapping.setAttributeName("column02");
        column02Mapping.setFieldName("COLUMN02");
        recordDescriptor.addMapping(column02Mapping);
        StructureMapping nest01Mapping = new StructureMapping();
        nest01Mapping.setAttributeName("nest01");
        nest01Mapping.setFieldName("NEST01");
        nest01Mapping.setReferenceClass(test.NestedRecord.class);
        recordDescriptor.addMapping(nest01Mapping);
        p.addDescriptor(recordDescriptor);
        
        ObjectRelationalDataTypeDescriptor nestedRecordDesc = new ObjectRelationalDataTypeDescriptor();
        nestedRecordDesc.descriptorIsAggregate();
        nestedRecordDesc.setJavaClass(test.NestedRecord.class);
        nestedRecordDesc.setAlias("NestedRecord");
        nestedRecordDesc.setStructureName(JDBC_SHADOW_TYPE1);
        DirectToFieldMapping value01Mapping = new DirectToFieldMapping();
        value01Mapping.setAttributeName("value01");
        value01Mapping.setFieldName("VALUE01");
        nestedRecordDesc.addMapping(value01Mapping);
        DirectToFieldMapping value02Mapping = new DirectToFieldMapping();
        value02Mapping.setAttributeName("value02");
        value02Mapping.setFieldName("VALUE02");
        nestedRecordDesc.addMapping(value02Mapping);
        p.addDescriptor(nestedRecordDesc);
        
        ds = p.createDatabaseSession();
        ds.setLogLevel(SessionLog.FINE);
        ds.login();
        try {
            ds.executeNonSelectingSQL(CREATE_PACKAGE);
            ds.executeNonSelectingSQL(CREATE_BODY);
            ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE1);
            ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE2);
        }
        catch (Exception e) {
            // ignore
        }
        
        testNestedRecordExecution();

        try {
            ds.executeNonSelectingSQL(DROP_PACKAGE);
            ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE2);
            ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE1);
        }
        catch (Exception e) {
            // ignore
        }
        ds.logout();
    }
    
    public static void testNestedRecordExecution() {
        
        PLSQLrecord recordNest = new PLSQLrecord();
        recordNest.setTypeName(PACKAGE_NAME + "." + NEST_RECORD_TYPE_NAME);
        recordNest.setCompatibleType(JDBC_SHADOW_TYPE1);
        recordNest.setJavaType(NestedRecord.class);
        recordNest.addField("VALUE01", JDBCTypes.VARCHAR_TYPE);
        recordNest.addField("VALUE02", JDBCTypes.NUMERIC_TYPE);

        // when a PLSQLrecord (or PLSQLcollection) databaseTypes is re-used across
        // the arguments, need separate instances - and that includes nested databaseTypes

        PLSQLrecord inRecord = new PLSQLrecord();
        inRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
        inRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
        inRecord.setJavaType(Record.class);
        inRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
        inRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
        inRecord.addField("NEST01", recordNest);

        PLSQLrecord outRecord = new PLSQLrecord();
        outRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
        outRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
        outRecord.setJavaType(Record.class);
        outRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
        outRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
        outRecord.addField("NEST01", recordNest.clone());

        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName(PACKAGE_NAME + "." + PROCEDURE_NAME);
        call.addNamedArgument("INPUT", inRecord);
        call.addNamedOutputArgument("OUTPUT", outRecord);
        ValueReadQuery query = new ValueReadQuery();
        query.addArgument("INPUT", Record.class);
        query.setCall(call);
        query.bindAllParameters();

        NestedRecord nRec = new NestedRecord();
        nRec.value01 = "nestedtest";
        nRec.value02 = new BigDecimal("123.456");
        Record rec = new Record();
        rec.column01 = "test";
        rec.column02 = new BigDecimal("789.012");
        rec.nest01 = nRec;
        
        List<Object> args = new ArrayList<Object>();
        args.add(rec);
        ds.executeQuery(query, args);
    }
}