Pass a nested table to Java from a pl/sql procedure

You Asked

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)

 

Object[] nestedObjects = (Object[]) nestedTable.getArray();

 

oracle.sql.STRUCT firstObject = (oracle.sql.STRUCT)nestedObjects[0];

String firstColumn = firstObject.getAttributes()[0];

 

Jpub is a great tool, but for simple tasks a handcrafted java code is cleaner and takes less 

dependecies

 

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941