Hi Tom,
Can I pass a nested table from pl/sql procedure to java , I am using JDBC.If yes can you
please provide me a simple example or point me to a documentation on this.My developers
don't know how to do this.
(I have seen an example on this side about how to pass an array from function (not from
procedure) to java ).
Thanks so much .
Ajeet
and we said...
If you have my book "Expert one on one Oracle", this will look familar -- it is a subset
of the example I have in the chapter on java stored procedures where I demonstrate how to
send back and forth all of the SQL types as well as collections of them:
ops$tkyte@ORA920> create or replace type numArray as table of number;
2 /
Type created.
ops$tkyte@ORA920> create or replace type dateArray as table of date;
2 /
Type created.
ops$tkyte@ORA920> create or replace type strArray as table of varchar2(255);
2 /
Type created.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package demo_passing_pkg
2 as
3 procedure pass( p_in in numArray, p_out out numArray )
4 as language java
5 name 'demo_passing_pkg.pass_num_array( oracle.sql.ARRAY,
6 oracle.sql.ARRAY[] )';
7
8 procedure pass( p_in in dateArray, p_out out dateArray )
9 as language java
10 name 'demo_passing_pkg.pass_date_array( oracle.sql.ARRAY,
11 oracle.sql.ARRAY[] )';
12
13
14 procedure pass( p_in in strArray, p_out out strArray )
15 as language java
16 name 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
17 oracle.sql.ARRAY[] )';
18 end;
19 /
Package created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set define off
ops$tkyte@ORA920>
ops$tkyte@ORA920> 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 {
12
13 private static void show_array_info( oracle.sql.ARRAY p_in )
14 throws SQLException
15 {
16 System.out.println( "Array is of type " +
17 p_in.getSQLTypeName() );
18 System.out.println( "Array is of type code " +
19 p_in.getBaseType() );
20 System.out.println( "Array is of length " +
21 p_in.length() );
22 }
23
24 public static void pass_num_array( oracle.sql.ARRAY p_in,
25 oracle.sql.ARRAY[] p_out )
26 throws SQLException
27 {
28 show_array_info( p_in );
29 java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray();
30
31 for( int i = 0; i < p_in.length(); i++ )
32 System.out.println( "p_in["+i+"] = " + values[i].toString() );
33
34 Connection conn = new OracleDriver().defaultConnection();
35 ArrayDescriptor descriptor =
36 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
37
38 p_out[0] = new ARRAY( descriptor, conn, values );
39
40 }
41
42 public static void
43 pass_date_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
44 throws SQLException
45 {
46 show_array_info( p_in );
47 java.sql.Timestamp[] values = (Timestamp[])p_in.getArray();
48
49 for( int i = 0; i < p_in.length(); i++ )
50 System.out.println( "p_in["+i+"] = " + values[i].toString() );
51
52 Connection conn = new OracleDriver().defaultConnection();
53 ArrayDescriptor descriptor =
54 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
55
56 p_out[0] = new ARRAY( descriptor, conn, values );
57
58 }
59
60 public static void
61 pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
62 throws java.sql.SQLException,IOException
63 {
64 show_array_info( p_in );
65 String[] values = (String[])p_in.getArray();
66
67 for( int i = 0; i < p_in.length(); i++ )
68 System.out.println( "p_in["+i+"] = " + values[i] );
69
70 Connection conn = new OracleDriver().defaultConnection();
71 ArrayDescriptor descriptor =
72 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
73
74 p_out[0] = new ARRAY( descriptor, conn, values );
75
76 }
77
78 }
79 /
Java created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set serveroutput on size 1000000
ops$tkyte@ORA920> exec dbms_java.set_output( 1000000 )
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> 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 OPS$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.
Dear Tom,
What if the nested table was constructed of composite type, for example:
CREATE OR REPLACE TYPE OBJ1 AS OBJECT (
COLUMN1 VARCHAR2(10),
COLUMN2 VARCHAR2(10));
CREATE OR REPLACE TYPE TAB1 AS TABLE OF OBJ1;
What changes would be required?
Objects in nested tables can be used the same way:
(consider nestedTable as oracle.sql.ARRAY - a nested table with object types inside)
Pass a nested table to Java from a pl/sql procedure
You AskedHi Tom,Can I pass a nested table from pl/sql procedure to java , I am using JDBC.If yes can you please provide me a simple example or point me to a documentation on this.M...