Hello, first I want to say thank you because I did not find any answer for this.
I know I should convert Document into any Sql type inside java procedure, but I did not find any equivalent but Clib but I can't find how to convert Document into Clob. If you can help me with this I will appreciate very much.
Thank you,
September 30, 2005 - 2:14 pm UTC
The following is a quote from Expert One on One Oracle that describes how to pass the primitive types to and from plsql/java.
<quote>
Passing Data
The routines I would like to implement in this example will be a series of procedures that accept an IN parameter, and an OUT (or IN OUT) parameter. We will write one for each type of interesting data type (the ones we will use frequently). They will demonstrate how to pass as inputs, and receive as outputs, each of these types correctly. Additionally, I would like to have some functions that show how to return some of these interesting types are well. To me the interesting types with Java are:
q Strings (up to 32k)
q Numbers (of any scale/precision)
q Dates
q Integers (binary_integers)
q Raws (up to 32k)
q Lobs (for all data > 32k)
q Arrays of Strings
q Arrays of Numbers
q Arrays of Dates
This list is a little different from the C-based external routines. Specifically, BOOLEAN is not represented. This is because there currently exists no mapping between the PL/SQL BOOLEAN type, and the Java types. We cannot use BOOLEANs in our Java external procedures as parameters.
There are also the arbitrarily complex data types you can create with the object relational extensions. For those, I would recommend you consider using the Oracle-supplied Java tool JPUBLISHER. This tool will create Java classes that wrap the object types for you automatically. For more information on JPUBLISHER please refer to the "Oracle8i
JPublisher User's Guide", part of the Oracle supplied documentation set. As with C-based external routines, we will not be going into object types in Java external routines, beyond the simple collections of scalar types.
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@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.
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.
The first routine passes the number type. Oracle Numbers will be passed to Java BigDecimal types. They could be passed to ints, strings and other types but could suffer from the loss of precision. BigDecimal can hold an Oracle number safely.
Notice how the OUT parameter is passed as an array of BigDecimal types to the Java layer. This will be true of all OUT parameters passed to Java. In order to modify a parameter passed to Java, we must pass an "array" of parameters (there will only be a single element in this array) and modify that array element. Below, in the implementation of the Java code, we'll see what that means in our source code.
tkyte@TKYTE816> create or replace package demo_passing_pkg
2 as
3 procedure pass( p_in in number, p_out out number )
4 as language java
5 name 'demo_passing_pkg.pass( java.math.BigDecimal,
6 java.math.BigDecimal[] )';
Next, Oracle Dates are mapped to the Timestamp type. Again they could have been mapped to a variety of different types - such as a String but in order to avoid any loss of information during the implicit conversions, I chose the Timestamp type which can accurately reflect the data contained in the Oracle Date type.
7
8 procedure pass( p_in in date, p_out out date )
9 as language java
10 name 'demo_passing_pkg.pass( java.sql.Timestamp,
11 java.sql.Timestamp[] )';
Varchar2's are very straightforward - they are passed to the java.lang.String type as you might expect.
12
13 procedure pass( p_in in varchar2, p_out out varchar2 )
14 as language java
15 name 'demo_passing_pkg.pass( java.lang.String,
16 java.lang.String[] )';
For the CLOB type, we use the Oracle supplied Java type oracle.sql.CLOB. Using this type, we'll easily be able to get the input and output streams used to read and write LOB types.
17
18 procedure pass( p_in in CLOB, p_out in out CLOB )
19 as language java
20 name 'demo_passing_pkg.pass( oracle.sql.CLOB,
21 oracle.sql.CLOB[] )';
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:
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[] )';
The next two routines demonstrate the mapping we will use for the RAW and INT types. The SQL RAW type will be mapped to the native Java type "byte". Likewise, we will use the native Java type "int" for simple integers:
38
39 procedure pass_raw( p_in in RAW, p_out out RAW )
40 as language java
41 name 'demo_passing_pkg.pass( byte[], byte[][] )';
42
43 procedure pass_int( p_in in number,
44 p_out out number )
45 as language java
46 name 'demo_passing_pkg.pass_int( int, int[] )';
47
And lastly, for completeness, we will demonstrate using functions to return the basic scalar types as well:
48 function return_number return number
49 as language java
50 name 'demo_passing_pkg.return_num() return java.math.BigDecimal';
51
52 function return_date return date
53 as language java
54 name 'demo_passing_pkg.return_date() return java.sql.Timestamp';
55
56 function return_string return varchar2
57 as language java
58 name 'demo_passing_pkg.return_string() return java.lang.String';
59
60 end demo_passing_pkg;
61 /
Package created.
This is basically the same package specification (minus the BOOLEAN) interface we used for the C-based external routines. In this example, I've put the binding layer right into the specification itself, to avoid having to code an entirely redundant package body, since every function is implemented in Java.
Now for the Java code that implements the above. We'll start with the definition of the demo_passing_pkg Java class first:
tkyte@TKYTE816> set define off
tkyte@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.*;
7 import oracle.sql.*;
8 import oracle.jdbc.driver.*;
9
10 public class demo_passing_pkg extends Object
11 {
This first routine demonstrates the only way to pass an OUT parameter to Java -- we actually pass an "array" and the first element in the array is the only element in the array. When we modify the value in the array, we will have modified the OUT parameter. That is why all of these methods have their second parameter as an array. p_out[0] is something we can set and it will be sent "out" of the method. Any changes we make to p_in on the other hand will not be returned.
The other interesting thing to note in this routine is the lack of need of an indicator variable! Java supports the concept null in its object types as does SQL and PL/SQL. It is not tri-valued logic like SQL is however - there is no "X IS NOT NULL" operation, we can just compare an object to null directly. Don't get confused and try to code something like "p_in <> NULL" in PLSQL -- it'll never work correctly!
12 public static void pass( java.math.BigDecimal p_in,
13 java.math.BigDecimal[] p_out )
14 {
15 if ( p_in != null )
16 {
17 System.out.println
18 ( "The first parameter is " + p_in.toString() );
19
20 p_out[0] = p_in.negate();
21
22 System.out.println
23 ( "Set out parameter to " + p_out[0].toString() );
24 }
25 }
26
The next routine operates on Oracle date types. This is virtually identical to the above routine but we use the methods of the Timestamp class to manipulate the date. Our goal in this routine is to add one month to the date:
27 public static void pass( java.sql.Timestamp p_in,
28 java.sql.Timestamp[] p_out )
29 {
30 if ( p_in != null )
31 {
32 System.out.println
33 ( "The first parameter is " + p_in.toString() );
34
35 p_out[0] = p_in;
36
37 if ( p_out[0].getMonth() < 11 )
38 p_out[0].setMonth( p_out[0].getMonth()+1 );
39 else
40 {
41 p_out[0].setMonth( 0 );
42 p_out[0].setYear( p_out[0].getYear()+1 );
43 }
44 System.out.println
45 ( "Set out parameter to " + p_out[0].toString() );
46 }
47 }
48
Now for the simplest of datatypes -- the String type. If you remember the C version with 6 formal parameters, null indicators, strlen's, strcpy's and so on -- this is trivial in comparison:
49 public static void pass( java.lang.String p_in,
50 java.lang.String[] p_out )
51 {
52 if ( p_in != null )
53 {
54 System.out.println
55 ( "The first parameter is " + p_in.toString() );
56
57 p_out[0] = p_in.toUpperCase();
58
59 System.out.println
60 ( "Set out parameter to " + p_out[0].toString() );
61 }
62 }
63
In the CLOB routine, we have a little bit of work to do. This routine implements a "copy" routine to show how to pass LOBS back and forth. It shows that in order to modify/read the contents of the LOB, we just use standard Java input/output stream types. In this example "is" is my input stream and "os" is the output stream. The logic in this routine does this copy 8k at a time. It just loops, read, write and then exits when no more to read:
64 public static void pass( oracle.sql.CLOB p_in,
65 oracle.sql.CLOB[] p_out )
66 throws SQLException, IOException
67 {
68 if ( p_in != null && p_out[0] != null )
69 {
70 System.out.println
71 ( "The first parameter is " + p_in.length() );
72 System.out.println
73 ( "The first parameter is '" +
74 p_in.getSubString(1,80) + "'" );
75
76 Reader is = p_in.getCharacterStream();
77 Writer os = p_out[0].getCharacterOutputStream();
78
79 char buffer[] = new char[8192];
80 int length;
81
82 while( (length=is.read(buffer,0,8192)) != -1 )
83 os.write(buffer,0,length);
84
85 is.close();
86 os.close();
87
88 System.out.println
89 ( "Set out parameter to " +
90 p_out[0].getSubString(1,80) );
91 }
92 }
93
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
Now for RAW data. Passing RAW data is, much like the String type, trivial. It is a very easy type to work with:
159 public static void pass( byte[] p_in, byte[][] p_out )
160 {
161 if ( p_in != null )
162 p_out[0] = p_in;
163 }
164
Passing ints is PROBLEMATIC and I do not recommend it. There is no way to pass NULL -- int's are "base datatypes" in Java, they are not objects - hence they cannot be null. Since there is no concept of a null indicator here -- we would have to actually pass our own if we wanted to support nulls and the PL/SQL layer would have to check a flag to see if the variable was null or not.
This is here for completeness but is not be a good idea, especially for IN parameters -- the Java routine cannot tell that it should not be reading the value since there is no concept of nullality!
165 public static void pass_int( int p_in, int[] p_out )
166 {
167 System.out.println
168 ( "The in parameter was " + p_in );
169
170 p_out[0] = p_in;
171
172 System.out.println
173 ( "The out parameter is " + p_out[0] );
174 }
175
And lastly, for the function. If you recall from the C based external procedures - this was much harder to implement in C. We had memory allocations, nulls to deal with, manual conversions from C types to Oracle types and so on. Each C routine was 10 or more lines of code. Here, it is as simple as a return statement:
176 public static String return_string()
177 {
178 return "Hello World";
179 }
180
181 public static java.sql.Timestamp return_date()
182 {
183 return new java.sql.Timestamp(0);
184 }
185
186 public static java.math.BigDecimal return_num()
187 {
188 return new java.math.BigDecimal( "44.3543" );
189 }
190
191 }
192 /
Java created.
That is pretty much it. In general, it is easier than in C due to the fact that Java does a lot of work under the covers for us. If you remember the C code, it was a little over 1000 lines to provide similar functionality. The memory allocation that we had to be so careful with in C is not a factor in Java - it'll throw an exception for us if we do something wrong. The null indicators that were prevalent in C are non-existent in Java. This does raise a problem if you bind to a non-object Java type, but as noted above in the PASS_INT routine, I would recommend against that if Nulls are part of your environment.
Now we are ready to call the routines since everything is in place. For example I can:
tkyte@TKYTE816> set serveroutput on size 1000000
tkyte@TKYTE816> exec dbms_java.set_output( 1000000 )
tkyte@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;
14 end;
15 /
Array is of type TKYTE.STRARRAY
Array is of type code 12
Array is of length 5
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.