Hi,
There are several threads in this forum about people having trouble with the array SQL type names that the Oracle JDBC driver recognizes (e.g., in a call setting up PLSQL procedure OUT parameters like "call.registerOutParameter(1, Types.ARRAY, "VARCHAR_NESTED_TAB_TYP");"). I've seen at least the following threads:
* vinay saini's "accessing package defined datatypes in oracle from JDBC" thread (
397549
* fwelland's "PL/SQL Tables as IN parameters???" thread (
394510
* Ernesto Marquina's "Table Type as OUT parameter" thread (
790290
* Nazneen Nahid's "Oracle PL/SQL type defined within a package can't be passed from JAVA" thread (
914827
* vinay saini's "mapping Table/VARRAY data type to JDBC data type" thread (
397448
I'm stuck in the middle of this myself, and found that something useful to look at is the PLSQL procedure that the driver uses to determine whether the type is there. You can run the following test PLSQL block with your schema name and type name to check on the database side whether it will work:
declare
schemaname varchar(255);
typename varchar(255);
typoid raw(255);
version integer;
tds long raw;
lds long raw;
begin
schemaname := 'MKT2_SMD9';
typename := 'VARCHAR_NESTED_TAB_TYP';
dbms_output.put_line('Result for '||schemaname||','||typename||'='||
dbms_pickler.get_type_shape(schemaname, typename, typoid, version, tds, lds));
end;
If the procedure returns 0, then the JDBC code should have no trouble picking up the array type and handling your call. If the procedure returns a nonzero number, the JDBC code using the same schemaname and typename will probably fail. This at least allows you to try a number of different schema and type names with the application and driver layers eliminated. Unfortunately, I have no idea how get_type_shape works, and it doesn't seem to be documented in Oracle's 9i docs, so I can't shed any light on why some things work and others don't.
Jim