Hi,
Kindly help me out with this.
I have a stored procedure written in Oracle :
PROCEDURE sp_bget_cell_system_info (
p_lac OUT numarray,
p_cellidvalue OUT numarray,
p_sysinfolac OUT numarray,
p_sysinfocellid OUT numarray,
p_sysinfomcc OUT numarray,
p_sysinfomnc OUT numarray
);
where numarray is a userdefined type as a table of number.
I want to execute this stored proc using
Java's CallableStatement and get the values returned by the database.
How do I register these out parameters using the registerOutParameter method?
I tried the following way:
//Code
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc
racle:thin:@smlcpt5:1521:T001";
Connection conn = DriverManager.getConnection(url,"system","manager");
Hashtable newMap = new Hashtable();
newMap.put("smlcpt5:1521:T001.NUMARRAY", Class.forName("NUMARRAY"));
conn.setTypeMap(newMap);
Map myMap = conn.getTypeMap();
System.out.println("SIZE IS = " + myMap.size());
String command = "{call sp_bget_cell_system_info(?,?,?,?,?,?)}";
CallableStatement cstmt = conn.prepareCall(command);
cstmt.registerOutParameter(1,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
cstmt.registerOutParameter(2,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
cstmt.registerOutParameter(3,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
cstmt.registerOutParameter(4,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
cstmt.registerOutParameter(5,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
cstmt.registerOutParameter(6,Types.ARRAY,"smlcpt5:1521:T001.NUMARRAY");
//Code end
But when I execute this, I get the following error :
SIZE IS = 1
Got an exception!
Error Message = invalid name
pattern: smlcpt5:1521:T001.NUMARRAY
java.sql.SQLException: invalid name pattern: smlcpt5:1521:T001.NUMARRAY
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:425)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:344)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1002)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:132)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:97)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:173)
at InsertStoredProcedure.main(InsertStoredProcedure.java:32)