Blog

My colleague asked me for help with a project, where java layer prepared data and passed it to a database layer, where PL/SQL functions awaited it. Some functions expected standard types like VARCHAR or NUMBER and implementation was smooth. But few functions expected complex database type on its input – table of types that reference other types. It took me some time to figure it out (StackOverflow question), how to prepare data and connection correctly. And I have learned several lessons I want to share in this article.

Let me introduce the example I will present here. There is a table Person with attributes name, surname, age and flag vip. The package oracle_types has two methods: get and add. The get method returns Antonín Holý record, the add method will store new entry into Person table with flag vip set when there is a keyword ‚actor‘ set.

CREATE TABLE PERSON (
    name    VARCHAR2(20),
    surname VARCHAR2(30),
    age     NUMBER(3),
    vip     CHAR(1)
);

CREATE OR REPLACE TYPE keyword_rec IS OBJECT (
    value VARCHAR2(20)
);

CREATE OR REPLACE TYPE keywords_rec IS TABLE OF keyword_rec;

CREATE OR REPLACE TYPE person_rec IS OBJECT (
    name     VARCHAR2(20),
    surname  VARCHAR2(30),
    age      NUMBER(3),
    keywords keywords_rec
);

CREATE OR REPLACE PACKAGE oracle_types AS
  FUNCTION add(p_rec person_rec, p_message OUT VARCHAR2) RETURN PLS_INTEGER;
  FUNCTION get RETURN person_rec;
end oracle_types;
/

CREATE OR REPLACE PACKAGE BODY oracle_types IS
  FUNCTION add(p_rec person_rec, p_message out varchar2) RETURN PLS_INTEGER IS
   v_vip NUMBER := 0;
  BEGIN
    FOR cur IN 1 .. p_rec.keywords.LAST
    LOOP
      IF p_rec.keywords(cur).value = 'actor' THEN
        v_vip := 1;
      END IF;
    END LOOP;
      IF v_vip > 0 THEN
        INSERT INTO PERSON(name,surname,age,vip) VALUES (p_rec.name, p_rec.surname, p_rec.age, '1');
      ELSE
        INSERT INTO PERSON(name,surname,age,vip) VALUES (p_rec.name, p_rec.surname, p_rec.age, '0');
      END IF;
   RETURN 1;
  END;

  FUNCTION get RETURN person_rec IS
   v_person person_rec;
   v_keywords keywords_rec;
  begin
    v_keywords := keywords_rec();
    v_keywords.EXTEND(2);
    v_keywords(1) := keyword_rec('scientist');
    v_keywords(2) := keyword_rec('inventor');
    v_person := person_rec('Antonin', 'Holy', 75, v_keywords);
    RETURN v_person;
  END;

BEGIN
  NULL;
end oracle_types;
/

declare
 v_result person_rec;
begin
  v_result := oracle_types.get();
  dbms_output.put_line('name = '||v_result.name);
  dbms_output.put_line('surname = '||v_result.surname);
  dbms_output.put_line('age = '||v_result.age);
  for cur in 1 .. v_result.keywords.last
  loop
    dbms_output.put_line('keyword = '||v_result.keywords(cur).value);
  end loop;
end;
/

The types must be public and they cannot be declared inside the package. Otherwise java will not see them. The java side will reside in WebLogic, so we need a remote interface for our business logic:

@Remote
public interface IOracleTest {
    public void performGet() throws Exception;
    public void performCall() throws Exception;
}

The stateless session bean implements this interface. It needs an access to database, so I declared datasource and let the application server to inject it.

@Stateless(mappedName = "test/oracle")
public class OracleTest implements IOracleTest {
    private static final Log log = LogFactory.getLog(OracleTest.class);

    @Resource(name = "jdbc/test")
    protected javax.sql.DataSource datasource;

The data source is to be created in Services/Data Sources menu option and JNDI name must match java resource name.

I will describe add method, because it is more complex – you need to construct an object tree the way that Oracle database expects. You must register your types in database connection. If the type is not public and it is located in different schema than your JDBC connection uses, you must prefix it with schema name.

conn = datasource.getConnection();
ArrayDescriptor keywordsArrayDesc = ArrayDescriptor.createDescriptor("KEYWORDS_REC", conn);
StructDescriptor personStructDesc = StructDescriptor.createDescriptor("PERSON_REC", conn);

The next step is to create array for person’s keywords. We have two rows with single column for KEYWORDS_REC type. The dimensions must be exactly the same like in database, otherwise an exception will occur. You have to pass objects that Oracle understands: String, BigDecimal, java.sql.Date etc. It is painfull to investigate, which object it does not like, because Oracle exceptions does not contain any identification of invalid value (SQLException: Inconsistent java and sql object types).

Object[][] keywordsAttribs = new Object[2][1];
keywordsAttribs[0][0] = "actor";
keywordsAttribs[1][0] = "producer";

Ok, it is time to prepare object for database procedure call. The person consists of four columns, therefore we need to create array of four objects. The last column is the table of KEYWORD_REC types. So we instantiate Oracle ARRAY object with two dimensional array and identification of used type (ArrayDescriptor keywordsArrayDesc). And we put it all together into Oracle STRUCT, again with type identification passed by StructDescriptor.

Object[] personAttribs = new Object[4];
personAttribs[0] = "Christian";
personAttribs[1] = "Bale";
personAttribs[2] = 39;
personAttribs[3] = new ARRAY(keywordsArrayDesc, conn, keywordsAttribs);
STRUCT struct = new STRUCT(personStructDesc, conn, personAttribs);

The last preparation step is to prepare database call. Our PL/SQL function returns two values, so we will specify their position. Again, if the position or returned type does not match database, SQLException will be raised.

prepStmt = conn.prepareCall("{ ? = call oracle_types.add(?, ?)}");
prepStmt.registerOutParameter(1, Types.INTEGER);
prepStmt.registerOutParameter(3, Types.VARCHAR);
prepStmt.setObject(2, struct);

Finally we will execute the statement and get the out variables.

prepStmt.execute();
int returnCode = prepStmt.getInt(1);
String message = prepStmt.getString(3);

The get method is very similar and I will leave it uncommented. Just one note – you need to have orai8n.jar in your classpath otherwise UTF conversion will not occur and fetched string will be unreadable.

conn = datasource.getConnection();
cs = conn.prepareCall(sql);

StructDescriptor keywordStructDesc = StructDescriptor.createDescriptor("KEYWORD_REC", conn);
StructDescriptor personStructDesc = StructDescriptor.createDescriptor("PERSON_REC", conn);
ResultSetMetaData metaData = personStructDesc.getMetaData();
cs.registerOutParameter(1, Types.STRUCT, "PERSON_REC");
cs.execute();

STRUCT output;
Object recordTmp = cs.getObject(1);
if (recordTmp instanceof Struct){
    output = (oracle.sql.STRUCT)(((weblogic.jdbc.wrapper.Struct)recordTmp).unwrap(Class.forName("oracle.sql.STRUCT")));
} else {
    output = (oracle.sql.STRUCT)recordTmp;
}

Object[] data = output.getAttributes();
int idx = 1;
for (Object tmp : data) {
    log.info(metaData.getColumnName(idx++) + " = " + tmp + ",---");
}

All the source code plus executable ear application is attached. To compile, you will need to provide listed jars.

PS I am interested how portable JDBC implementation would look like. Please comment code differences in Java.

Blog bitcoin address: 1GQBYqZwiHT72UrLCCSv4j6WkK65FjTPJk