StoredProcedure with advanced Oracle types

from http://www.eclipse.org/forums/index.php?t=msg&th=164573&start=0&

Suppose we have an Address 'type' -

  CREATE OR REPLACE TYPE ADDR_TYPE AS OBJECT (
    STREET VARCHAR2(40),
    CITY VARCHAR2(40),
    PROV VARCHAR2(40)
  )

that is used in our Employee table -

  CREATE OR REPLACE TABLE EMP_W_ADDR (
    EMPNO NUMBER(7) NOT NULL,
    FNAME VARCHAR2(40),
    LNAME VARCHAR2(40),
    ADDRESS ADDR_TYPE,
    PRIMARY KEY (EMPNO)
  )

This can be used in a StoredProcedure -

  CREATE OR REPLACE PROCEDURE GET_EMPS_BY_PROV(X IN ADDR_TYPE, Y OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN Y FOR SELECT * FROM EMP_W_ADDR ea WHERE ea.ADDRESS.PROV LIKE X.PROV;
  END;

Populating the table -
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (1, 'Mike', 'Norman', ADDR_TYPE('Somewhere','Nepean','Ont'));
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (2, 'Rick', 'Barkhouse', ADDR_TYPE('Over the','Hull','Que'));
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (3, 'Blaise', 'Doughan', ADDR_TYPE('rainbow','Ottawa','Ont'));

To invoke the 'GET_EMPS_BY_PROV' procedure, we need some (simple) model classes
{for demo purposes, I put everything in the package 'test' but that can be changed easily ...}

package test;
public class Address {

    public String street;
    public String city;
    public String province;
    
    public Address() {
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(street);
        sb.append(" ");
        sb.append(city);
        sb.append(" ");
        sb.append(province);
        return sb.toString();
    }
}

public class Employee {

    public Integer id;
    public String firstName;
    public String lastName;
    public Address address;

    public Employee() {
    }
    
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("[");
        sb.append(id);
        sb.append("] ");
        sb.append(firstName);
        sb.append(" ");
        sb.append(lastName);
        sb.append(" ");
        sb.append(address);
        return sb.toString();
    }
}

A simple test class shows mappings for each type as well as how to build a query to execute the stored procedure.
{NB: all Oracle database artifacts - type names, table names, etc. should always be represented in
EclipseLink metadata in UPPERCASE}

//javase imports
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

//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.oracle.Oracle11Platform;
import org.eclipse.persistence.queries.ReadAllQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;
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 OracleObjectTypeTest {

    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 ADDR_TYPE_NAME = "ADDR_TYPE";
    static final String EMP_TABLE_NAME = "EMP_W_ADDR";
    static final String PROCEDURE_NAME = "GET_EMPS_BY_PROV";
    static final String CREATE_TYPE =
        "CREATE TYPE " + ADDR_TYPE_NAME + " AS OBJECT (\n" +
          "STREET VARCHAR2(40),\n" +
          "CITY VARCHAR2(40),\n" +
          "PROV VARCHAR2(40)\n" +
        ");";
    static final String CREATE_EMP_TABLE = 
        "CREATE TABLE EMP_W_ADDR (\n" +
          "EMPNO NUMBER(7) NOT NULL,\n" +
          "FNAME VARCHAR2(40),\n" +
          "LNAME VARCHAR2(40),\n" +
          "ADDRESS ADDR_TYPE,\n" +
          "PRIMARY KEY (EMPNO)\n" +
        ")";
    static final String CREATE_PROCEDURE =
        "CREATE PROCEDURE " + PROCEDURE_NAME + "(X IN " + ADDR_TYPE_NAME + 
        		", Y OUT SYS_REFCURSOR) AS\n" +
        "BEGIN\n" +
          "OPEN Y FOR SELECT * FROM " + EMP_TABLE_NAME + " ea WHERE ea.ADDRESS.PROV LIKE X.PROV;\n" +
        "END;\n";
    static final String INSERT_ROW1 = 
        "INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
            "(1, 'Mike', 'Norman', ADDR_TYPE('Somewhere','Nepean','Ont'))";
    static final String INSERT_ROW2 = 
        "INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
        "(2, 'Rick', 'Barkhouse', ADDR_TYPE('Over the','Hull','Que'))";
    static final String INSERT_ROW3 = 
        "INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
        "(3, 'Blaise', 'Doughan', ADDR_TYPE('rainbow','Ottawa','Ont'))";
    static final String DELETE_ROWS = 
        "DELETE FROM " + EMP_TABLE_NAME;
    static final String DROP_TYPE =
        "DROP TYPE " + ADDR_TYPE_NAME;
    static final String DROP_EMP_TABLE =
        "DROP TABLE " + EMP_TABLE_NAME;
    static final String DROP_PROCEDURE = 
        "DROP PROCEDURE " + PROCEDURE_NAME;
    
    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 addrTypeDescriptor = new ObjectRelationalDataTypeDescriptor();
        addrTypeDescriptor.descriptorIsAggregate();
        addrTypeDescriptor.setJavaClass(test.Address.class);
        addrTypeDescriptor.setAlias("Address");
        addrTypeDescriptor.setStructureName(ADDR_TYPE_NAME);
        DirectToFieldMapping streetMapping = new DirectToFieldMapping();
        streetMapping.setAttributeName("street");
        streetMapping.setFieldName("STREET");
        addrTypeDescriptor.addMapping(streetMapping);
        DirectToFieldMapping cityMapping = new DirectToFieldMapping();
        cityMapping.setAttributeName("city");
        cityMapping.setFieldName("CITY");
        addrTypeDescriptor.addMapping(cityMapping);
        DirectToFieldMapping provinceMapping = new DirectToFieldMapping();
        provinceMapping.setAttributeName("province");
        provinceMapping.setFieldName("PROV");
        addrTypeDescriptor.addMapping(provinceMapping);
        p.addDescriptor(addrTypeDescriptor);

        ObjectRelationalDataTypeDescriptor empDesc = new ObjectRelationalDataTypeDescriptor();
        empDesc.setJavaClass(test.Employee.class);
        empDesc.setAlias("Employee");
        empDesc.setTableName(EMP_TABLE_NAME);
        empDesc.addPrimaryKeyFieldName("EMPNO");
        DirectToFieldMapping empnoMapping = new DirectToFieldMapping();
        empnoMapping.setAttributeName("id");
        empnoMapping.setFieldName("EMPNO");
        empDesc.addMapping(empnoMapping);
        DirectToFieldMapping fnameMapping = new DirectToFieldMapping();
        fnameMapping.setAttributeName("firstName");
        fnameMapping.setFieldName("FNAME");
        empDesc.addMapping(fnameMapping);
        DirectToFieldMapping lnameMapping = new DirectToFieldMapping();
        lnameMapping.setAttributeName("lastName");
        lnameMapping.setFieldName("LNAME");
        empDesc.addMapping(lnameMapping);
        StructureMapping addrMapping = new StructureMapping();
        addrMapping.setAttributeName("address");
        addrMapping.setFieldName("ADDRESS");
        addrMapping.setReferenceClass(test.Address.class);
        empDesc.addMapping(addrMapping);
        p.addDescriptor(empDesc);

        ds = p.createDatabaseSession();
        ds.setLogLevel(SessionLog.FINE);
        ds.login();
        try {
            ds.executeNonSelectingSQL(CREATE_TYPE);
            ds.executeNonSelectingSQL(CREATE_EMP_TABLE);
            ds.executeNonSelectingSQL(CREATE_PROCEDURE);
            ds.executeNonSelectingSQL(INSERT_ROW1);
            ds.executeNonSelectingSQL(INSERT_ROW2);
            ds.executeNonSelectingSQL(INSERT_ROW3);
        }
        catch (Exception e) {
            // ignore
        }

        testProcedureExecution();

        try {
            ds.executeNonSelectingSQL(DELETE_ROWS);
            ds.executeNonSelectingSQL(DROP_EMP_TABLE);
            ds.executeNonSelectingSQL(DROP_PROCEDURE);
            ds.executeNonSelectingSQL(DROP_TYPE);
        }
        catch (Exception e) {
            // ignore
        }
        ds.logout();
    }
    
    @SuppressWarnings("unchecked")
    public static void testProcedureExecution() {
        
        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.addArgument("X", String.class);
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName(PROCEDURE_NAME);
        call.addNamedArgument("X", "X", Types.STRUCT, ADDR_TYPE_NAME, Address.class);
        call.useNamedCursorOutputAsResultSet("Y");
        raq.addCall(call);
        
        List<Object> args = new ArrayList<Object>();
        Address ontarioAddresses = new Address();
        ontarioAddresses.province = "Ont";
        args.add(ontarioAddresses);
        Vector<Employee> ontarioEmployees = (Vector<Employee>)ds.executeQuery(raq, args);
        for (Employee e : ontarioEmployees) {
            System.out.println(e);
        }
    }

}