Passing Oracle Object as a parameter

[Lisa M.]

Hi all,

Please help with my JAVA - Oracle problem. I'm trying to return an Oracle
(9i) object to Java from an Oracle stored procedure, but keep getting a
'java.sql.SQLException: Invalid column type' error message. I think I must be
missing a step, but don't know what it would be. The examples I've found all
show saving the object into an Oracle table, and then using getObject() to get
it out of the result set. I don't want to save the information in the
database, just pass the object back and forth. I'm trying to use a strongly
typed object using a JAVA object that implements SQLData to match my Oracle
object.

My Oracle Object:

Create or Replace TYPE UWSOU.AU_UserApplication_t AS OBJECT (

userId varchar2(10),
applicationCode varchar2(10),
approvalStatus varchar2(1),
userApplicationCount integer,
userCount integer,
applicationCount integer
);
\

=================================================================

My JAVA Object:

package edu.uwsa.dataAccess.businessObjects;

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

public class UserApplicationSQL implements SQLData {

private static final String typeName="UWSOU.AU_UserApplication_t";

private String userId;
private String applicationCode;
private String approvalStatus;
private int userApplicationCount;
private int userCount;
private int applicationCount;

public UserApplicationSQL() {
}

public String getSQLTypeName() {
return typeName;
}

public void readSQL(SQLInput inStream, String typeName) throws
SQLException{
userId = inStream.readString();
applicationCode = inStream.readString();
approvalStatus = inStream.readString();
userApplicationCount = inStream.readInt();
userCount = inStream.readInt();
applicationCount = inStream.readInt();
}

public void writeSQL(SQLOutput outStream) throws SQLException{
outStream.writeString(userId);
outStream.writeString(applicationCode);
outStream.writeString(approvalStatus);
outStream.writeInt(userApplicationCount);
outStream.writeInt(userCount);
outStream.writeInt(applicationCount);
}
(snipped Setters & Getters, but they exist in the real object)
}

=================================================================

My Java Program:

package edu.uwsa.dataAccess.businessObjects;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;

class TesterUDTelm {
public TesterUDTelm() throws Exception {
super();
}

public static void main(String[] args) throws Exception {

UserApplicationSQL uas = new UserApplicationSQL(); // Instantiate my object

Connection conn = PooledConnection.getConnection();
try {
// Get the connection map
Map typeMap = conn.getTypeMap();
Class c =
Class.forName("edu.uwsa.dataAccess.businessObjects.UserApplicationSQL");
// Add mapping from Oracle Object to JAVA Object
typeMap.put("UWSOU.AU_UserApplication_t",c);
CallableStatement cs = null;
cs = conn.prepareCall("{ call p_da_EditColumn_elm(?,?,?) }");
cs.registerOutParameter(1, Types.INTEGER, 0);
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.OTHER);
// I've also tried Types.JAVA_OBJECT
cs.setLong(1, new Long(1).longValue());
cs.setString(2, "Test");
System.out.println("execute return = "+cs.execute());
System.out.println("Edit Number: "+cs.getLong(1));
System.out.println("Message: "+cs.getString(2));
}

catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getErrorCode());
}
}
}

=================================================================
The Error:

connection pool created
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273)
at
oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:4560)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:225)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:350)
at
edu.uwsa.dataAccess.businessObjects.TesterUDTelm.main(TesterUDTelm.java:30)
17004

Any help is much appreciated!

Lisa

[Tom Hester]

I tried posting this once and it did not go for some reason, so here goes
again.

You show the 'create type' statement but no corresponding 'create table' to
contain the type.

"Lisa M." <lmcgis...@hotmail.com> wrote in message
news:k95d10lpj02g5fguo...@4ax.com...