Reading an Oracle ARRAY from a stored procedure out param

[whitney]

Can you tell me how I can read an Oracle ARRAY that is returned as an out parameter from a stored procedure. I am using a ParameterMapper to format the in parameters (one of which is an Oracle ARRAy as well). This works well because I have the Oracle connection in the ParameterMapper. But when I am back in the execute method, I don't know how to get the connection so that I can create an ArrayDescriptor and read the ARRAY from the output map. Can anyone tell me how this is best done?

Thanks,
Whitney

[trisberg]

I declare an out parameter of type ARRAY and also specify the name of the Oracle type:

			declareParameter(new SqlOutParameter("stringArray", Types.ARRAY, "SCOTT.T_STRING_ARRAY"));

Then I access this the following way in my StoredProcedure implementation:

		public String[] executeAndGetArray(Map parameters) {
			Map out = execute(parameters);
			Array retValue = (Array) out.get("stringArray");
			String[] retArray = null;
			try {
				retArray = (String[]) retValue.getArray();
			}
			catch (SQLException se) {
				throw new TypeMismatchDataAccessException("Failed to get array", se);
			}
		
			return retArray;
		
		}

[whitney]

Thanks for the reply. Although this approach may work for arrays of Strings, it does not seem to work for arrays of Oracle object.

I have an error object defined as:

CREATE OR REPLACE TYPE ERRORRECTYPE AS OBJECT
 (RECTYPE VARCHAR2(100)
 ,RECID VARCHAR2(32)
 ,ERROR VARCHAR2(1000)
 ,TYPE VARCHAR2(30)
 ,NAMETYPE VARCHAR2(30)
 ,NAME VARCHAR2(255)
 ,EFFECTIVEDATE DATE
 )

I used JPublisher to generate a Java class to map this type to. It is called OracleError.

Then my array is defined as:

CREATE OR REPLACE TYPE ERRORLISTTYPE AS TABLE OF ERRORRECTYPE

My Java code looks like this:

declareParameter(new SqlOutParameter("PERRORLIST", Types.ARRAY, "ERRORLISTTYPE"));

public String execute(String entityType, Date effectiveDate,
  Date terminationDate, String user, List roles) {

  Map out = execute(new ParameterMapperImpl(entityType,
    effectiveDate, terminationDate, user, roles));

  Array errors = (Array) out.get("PERRORLIST");
  OracleError oracleErrors[] = null;
  try {
    Object array = errors.getArray();
    oracleErrors = (OracleError[]) array;
  } catch (Throwable e) {
    e.printStackTrace();
  }

  return (String) out.get("PENTITYID");

}

I am also setting the connection type map with:

Map typeMap = new HashMap();
typeMap.put("ERRORRECTYPE", OracleError.class);
connection.setTypeMap(typeMap);

However, the "errors.getArray()" call throws this exception:

java.lang.NullPointerException
	at oracle.jdbc.driver.OracleConnection.safelyGetClassForName(OracleConnection.java:4974)
	at oracle.jdbc.driver.OracleConnection.addClassMapEntry(OracleConnection.java:2801)
	at oracle.jdbc.driver.OracleConnection.addDefaultClassMapEntriesTo(OracleConnection.java:2792)
	at oracle.jdbc.driver.OracleConnection.initializeClassMap(OracleConnection.java:2478)
	at oracle.jdbc.driver.OracleConnection.ensureClassMapExists(OracleConnection.java:2472)
	at oracle.jdbc.driver.OracleConnection.getTypeMap(OracleConnection.java:2778)
	at oracle.sql.ARRAY.getMap(ARRAY.java:768)
	at oracle.sql.ARRAY.getArray(ARRAY.java:275)
        ...

I'm thinking that the getArray() call needs the connection, but connection has been closed by this point. Any thoughts would be helpful.

Thank you,
Whitney

[whitney]

It looks like my theory is correct. The JdbcTemplate is closing the connection in

execute(CallableStatementCreator, CallableStatementCallback)

If I create a custom DataSource that responds with false to the shouldClose(Connection) method then I can prevent the JdbcTemplate from closing the connection. Then my code works.

This seems like a hack, but it works. Is there a better way?

[trisberg]

Looks like we will have to add support for this - something similar to what we do for returned resultsets with a specific interface that implements a callback method.

Something like

SqlReturnType() {
  public Object getTypeValue(Connection con, int sqlType, String typeName, Object typeObject) {
    //do the mapping to a custom object here
  }
}

I'll try to add it this week.

[whitney]

That sounds great. Thank you very much!

[trisberg]

I have implemented the support for a custom type being returned from a stored procedure. It is in CVS now and will be part of the next release 1.1

The interface you need to implement is SqlReturnType and the single method is getTypeValue. This method has the following signature: getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)

The implementation of this interface should be passed as the 4th parameter to a SqlOutParameter declaration.

Here is an example of how to use it -

1. The Oracle custom object type

CREATE TYPE PERSON_TYPE 
AS OBJECT ( id NUMBER(10), name VARCHAR2(50), age NUMBER )

2. A simple Oracle stored proc to test this

CREATE OR REPLACE PROCEDURE Get_Person (out_p OUT PERSON_TYPE)
AS
   p PERSON_TYPE;
BEGIN
   p := person_type(1, 'Bubba', 33);
   out_p := p;
END;
/

3. A Java class to map the PERSON_TYPE to

import java.math.BigDecimal;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Person implements SQLData {
	private long id;
	private String name;
	private BigDecimal age;

	public String getSQLTypeName() throws SQLException {
		return "PERSON_TYPE";
	}

	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeLong(id);
		stream.writeString(name);
		stream.writeBigDecimal(age);
	}

	public void readSQL(SQLInput stream, String typeName) throws SQLException {
	    id = stream.readLong();
	    name = stream.readString();
	    age = stream.readBigDecimal();
	}
	
	public BigDecimal getAge() {
		return age;
	}
	public void setAge(BigDecimal age) {
		this.age = age;
	}
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String toString() {
		return "Person: " + id + " " + name + " " + age;
	}
}

4. The implementation of Spring's StoredProcedure
We are using the new constructor for the SqlOutParameter - SqlOutParameter(String name, int type, String typeName, SqlReturnType sqlReturnType)
In this code you can see the implementation details of the getTypeValue callback method. This method is responsible for getting the object from the CallableStatement and also for any custom setup that is needed. If you get an ARRAY back, then you would have to retreive it and map each item in it to a custom object that could be put into a List or an array.

	public class GetPersonProc extends StoredProcedure {
		private static final String sql = "GET_PERSON";
		
		public GetPersonProc(DataSource dataSource) {
			super(dataSource, sql);
			declareParameter(new SqlOutParameter("p", OracleTypes.STRUCT, "PERSON_TYPE", 
				new SqlReturnType() {
					public Object getTypeValue(CallableStatement cs,
							int paramIndex, int sqlType, String typeName)
							throws SQLException {
						Connection con = cs.getConnection();
						Map typeMap = con.getTypeMap();
						typeMap.put(typeName, Person.class);
						Object o = cs.getObject(paramIndex);
						return o;
					}
				}
			));
		}
	
	}

5. Finally the calling code snippet

		GetPersonProc sp = new GetPersonProc(ds);
		Map out = sp.execute(new HashMap());
		System.out.println(out);

[califa_of_cordoba]

Hi everybody,

I can't use this code with a PL SQL table.

This is my Table Type in PL /SQL:

TYPE TOneRecord IS RECORD (transaction_id VARCHAR2(40), customer_id VARCHAR2(30), application_id VARCHAR2(30));
TYPE TStatRecords IS TABLE OF TOneRecord;

this is my procedure...

PROCEDURE get_transaction_logs_summary (start_time IN DATE, end_time IN DATE, log_summary OUT TStatRecords);


For my table type (TStatRecords) I have created this class:


public class StatRecords implements SQLData{



private Vector records = new Vector();



public String getSQLTypeName() throws SQLException{
return "STATS_PROCEDURES.TStatRecords";
}

public void writeSQL(SQLOutput stream) throws SQLException{
stream.writeArray((java.sql.Array) records);
}


public void readSQL(SQLInput stream, String typeName) throws SQLException{
records = (Vector) stream.readArray();
}


}


and I tried to call my store procedure in this way:


setSql(GET_TRANSACTION_LOGS_SUMMARY);
declareParameter(new SqlParameter(START_TIME, Types.DATE));
declareParameter(new SqlParameter(END_TIME, Types.DATE));

declareParameter(new SqlOutParameter(SUMMARY_LOG,Types.ARRAY, "TStatRecords",
new SqlReturnType() {
public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException
{
try
{
Connection con = cs.getConnection();
Map typeMap = con.getTypeMap();
typeMap.put(typeName, StatRecords.class);
Object o = cs.getObject(paramIndex);
return o;
} catch (Exception e)
{
throw new SQLException(e.toString());
}
}
}
));
compile();

Finally when i tried to execute it i received this exception:

[Parametertypen conflicteren.: sqlType=2003]


Could anyone help me? Thanks in advance


Alberto

[lraynal]

Hi,

Just a remark, since I'm so disappointed I lost time on this:

I don't see the point in forcing to declare types outside of package if you want to be able to use them in Java, as it is the case on oracle 9.2 (found the confirmation on another forum).
You need to have special rights to do this and it's a maintenance problem.

Thanks

[prash57]

trisberg trisberg
Senior Member
Spring Team

4. The implementation of Spring's StoredProcedure
We are using the new constructor for the SqlOutParameter - SqlOutParameter(String name, int type, String typeName, SqlReturnType sqlReturnType)
In this code you can see the implementation details of the getTypeValue callback method. This method is responsible for getting the object from the CallableStatement and also for any custom setup that is needed. If you get an ARRAY back, then you would have to retreive it and map each item in it to a custom object that could be put into a List or an array.

How would i go about mapping the each element of an ARRAY to a Person object ? Do I need to do it the hard way, by settting each attribute of the STRUCT to each method in the Person object ? or is there an easy way of casting a STRUCT to the Person object ?