| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: how to map String[] returned from Java sp to PL/SQL
In article <15071bed.0204191017.50c8335c_at_posting.google.com>,
kboyanov_at_printrak.com says...
>
>Hi All,
>
>I have a function in Java returning String[] that I want
>to get in my PL/SQL procedure.
>
>Anybody having an idea what type should I use to map String[] in Oracle PL/SQL
>to?
>
>For example:
>In Java code I have:
>static void String[] MyJavaFunc(String strPar)
>{
> String[] arr;
> //obtaining arr;
> return arr;
>}
>
>In PL/SQL my mapping is:
> FUNCTION my_func (str_par VARCHAR2) return ?????
> AS LANGUAGE JAVA NAME
> 'MyJavaClass.MyJavaFunc(java.lang.String strPar) return String[]';
>
>
>MTIA,
>Krassimir
Here is a very small, chopped up extract from my book -- you might find the rest of the chapter useful as well (on java stored procs):
....
The Java class we will implement is a re-implementation of the C-based external
routine we coded earlier, only this time it is, of course, written in Java.
We?ll begin with the SQL definition of our three collection types ? these are
the same definitions we used in the C External Procedures example as well:
...
tkyte_at_TKYTE816> create or replace type strArray as table of varchar2(255); Type created.
Now, the PL/SQL call specification for our example will be as follows. It will be a series of overloaded procedures and functions to test passing parameters to and from Java stored procedures. Each routine has n IN and an OUT parameter to show data being sent to and returned from the Java code.
...
Now for the collection types ? we see we will use the same Oracle supplied type regardless of the type of collection we are actually passing. That is why in this case, the Java routines are not overloaded routines as they have all been so far (all of the Java routines have been named demo_passing_pkg.pass so far). Since each of the collection types are passed as the exact same Java type ? we cannot use overloading in this case ? rather we have a routine named after the type we are actually passing:
...
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[] )';
...
Now for the Java code that implements the above. We?ll start with the definition of the demo_passing_pkg Java class first:
tkyte_at_TKYTE816> set define off
tkyte_at_TKYTE816> create or replace and compile 2 java source named "demo_passing_pkg" 3 as
4 import java.io.*; 5 import java.sql.*; 6 import java.math.*;
....
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 }
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:
...
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
...
Now we are ready to call the routines since everything is in place. For example I can:
tkyte_at_TKYTE816> set serveroutput on size 1000000 tkyte_at_TKYTE816> exec dbms_java.set_output( 1000000 )
tkyte_at_TKYTE816> declare
2 l_in strArray := strArray();
3 l_out strArray := strArray();
4 begin
5 for i in 1 .. 5 loop
6 l_in.extend;
7 l_in(i) := 'Element ' || i;
8 end loop;
9
10 demo_passing_pkg.pass( l_in, l_out );
11 for i in 1 .. l_out.count loop
12 dbms_output.put_line( 'l_out(' || i || ') = ' || l_out(i) );
13 end loop;
p_in[0] = Element 1 p_in[1] = Element 2 p_in[2] = Element 3 p_in[3] = Element 4 p_in[4] = Element 5 l_out(1) = Element 1 l_out(2) = Element 2 l_out(3) = Element 3 l_out(4) = Element 4 l_out(5) = Element 5
PL/SQL procedure successfully completed.
The first eight lines of output were generated by the Java routine, the last
five by PL/SQL. This shows that we were able to pass the array from PL/SQL to
Java, and receive an array back just as easily. The Java routine simply copied
the input array to the output array after printing out the array meta-data and
values.
.........
-- Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Apr 20 2002 - 14:36:38 CDT
 |
 |