... does not discuss my problem.
....
sorry?? I mean, given you didn't ask the question or anything?
but you know what, actually, I just read it.
If the question is
Ok, plsql has passed my java routine an array, how does java access that array, the link you say "doesn't discuss my problem" actually does. Tell me, what is the difference between an OUT parameter coming back from plsql and an input to a java routine - as far as mapping and accessing go??
anyway, short excerpt from Expert one on one Oracle - of some of the code. The plsql spec for passing collections:
tkyte@TKYTE816> create or replace type numArray as table of number;
Type created.
tkyte@TKYTE816> create or replace type dateArray as table of date;
Type created.
tkyte@TKYTE816> create or replace type strArray as table of varchar2(255);
Type created.
...
22
23 procedure pass( p_in in numArray, p_out out numArray )
24 as language java
25 name 'demo_passing_pkg.pass_num_array( oracle.sql.ARRAY,
26 oracle.sql.ARRAY[] )';
27
28 procedure pass( p_in in dateArray, p_out out dateArray )
29 as language java
30 name 'demo_passing_pkg.pass_date_array( oracle.sql.ARRAY,
31 oracle.sql.ARRAY[] )';
32
33
34 procedure pass( p_in in strArray, p_out out strArray )
35 as language java
36 name 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
37 oracle.sql.ARRAY[] )';
....
and the java stored procedures:
This next routine is a private (internal) routine. It simply prints out meta-data about the oracle.sql.ARRAY that is passed to it. Each of the three array types we send down to Java will make use of this routine just to report back what size/type they are:
94 private static void show_array_info( oracle.sql.ARRAY p_in )
95 throws SQLException
96 {
97 System.out.println( "Array is of type " +
98 p_in.getSQLTypeName() );
99 System.out.println( "Array is of type code " +
100 p_in.getBaseType() );
101 System.out.println( "Array is of length " +
102 p_in.length() );
103 }
104
Now for the routines that manipulate the arrays. Arrays are easy to use once you figure out how to get the data out of them and then back in. Getting the data out is very easy -- the "getArray()" method will return the base data array for us. We simply need to cast the return value from getArray to the appropriate type and we then have a Java array of that type. Putting the data back into an array is a little more complex. We must first create a descriptor (meta-data) about the array and then create a new array object with that descriptor and the associated values. The following set of routines demonstrate this for each of the array types in turn. Note that the code is virtually identical ¿ with the exception of the times we actually access the Java array of data. All these routines do is show us the meta-data of the oracle.sql.ARRAY type, print out the contents of the array, and finally copy the input array to the output array:
105 public static void pass_num_array( oracle.sql.ARRAY p_in,
106 oracle.sql.ARRAY[] p_out )
107 throws SQLException
108 {
109 show_array_info( p_in );
110 java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray();
111
112 for( int i = 0; i < p_in.length(); i++ )
113 System.out.println( "p_in["+i+"] = " + values[i].toString() );
114
115 Connection conn = new OracleDriver().defaultConnection();
116 ArrayDescriptor descriptor =
117 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
118
119 p_out[0] = new ARRAY( descriptor, conn, values );
120
121 }
122
123 public static void
124 pass_date_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
125 throws SQLException
126 {
127 show_array_info( p_in );
128 java.sql.Timestamp[] values = (Timestamp[])p_in.getArray();
129
130 for( int i = 0; i < p_in.length(); i++ )
131 System.out.println( "p_in["+i+"] = " + values[i].toString() );
132
133 Connection conn = new OracleDriver().defaultConnection();
134 ArrayDescriptor descriptor =
135 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
136
137 p_out[0] = new ARRAY( descriptor, conn, values );
138
139 }
140
141 public static void
142 pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
143 throws java.sql.SQLException,IOException
144 {
145 show_array_info( p_in );
146 String[] values = (String[])p_in.getArray();
147
148 for( int i = 0; i < p_in.length(); i++ )
149 System.out.println( "p_in["+i+"] = " + values[i] );
150
151 Connection conn = new OracleDriver().defaultConnection();
152 ArrayDescriptor descriptor =
153 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
154
155 p_out[0] = new ARRAY( descriptor, conn, values );
156
157 }
158