Thanks for the question, Ajeet.

Asked: March 29, 2003 - 3:29 pm UTC

Last updated: November 18, 2008 - 7:43 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

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 Tom 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.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More advanced example request

Brian Etheridge, November 01, 2005 - 7:38 am UTC

Hi,
Your response to 'Can I Pass a nested table to Java from a pl/sql procedure' was excellent. I am using this technique successfully, however, I have suddenly hit a problem when I try to pass a nested table of my own objects:

create or replace type MyObject as object(
TypeName varchar2(128),
Name varchar2(128),
OtherName varchar2(128),
Comment varchar2(128)
);

create or replace type MyObjectList as table of MyObject;

Everything looks fine, everything loads OK into Oracle (10g rel 1), but I get a ClassCastException at runtime in the Java code on the line:

MyObject[] myObjectAry = (MyObject[])p_myObjectArray.getArray();

Should I be able to pass my objects or are we restricted to Oracle classes, primitives or certain Java classes, like String?

November 01, 2005 - 10:39 am UTC

I'd be looking to jpublisher at this point to have it generate the mapping class (if I did this at all....)

</code> http://docs.oracle.com/docs/cd/B10501_01/java.920/a96658/toc.htm <code>

Composite nested table

A reader, November 18, 2008 - 7:02 am UTC

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?

November 18, 2008 - 7:43 pm UTC

read up one review... jpublisher.

Objects in nested tables

Tamas Foldi, January 25, 2009 - 6:03 am UTC

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

Using Nested Table in Java

shyam, October 23, 2010 - 5:57 am UTC

Hi Tom,

I have requirement in which i need to call a java procedure in which i need to pass 1 parameter integer and the return type of that function is nested.

I am using Callable statement to call the procedure,but i am not aware what data-type need to choose in the input and how to handle the result of the procedure i.e how to get hold of result.

ARRAY array;
try {
cs = con.prepareCall(pvsolprocedure);
cs.registerOutParameter(1,Types.ARRAY);
cs.setString(2,"india");
cs.execute();
array=(ARRAY) cs.getArray(1);

This is my code snippet which is resulting unsupported exception

Thanks,
Shyam.S

Retrieving CLOB from object

Rafał Sobieraj, August 06, 2012 - 10:19 am UTC

Hi Tom,

How can I get contents of a CLOB from an oracle object?
My object definition is:

create or replace type zip_file is object
(
FILE_NAME VARCHAR2(100),
FILE_CONTENTS CLOB
);

create or replace type zip_files as table of zip_file;

... and code is:
Object[] nestedObjects = (Object[]) zip_files.getArray();

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

I am able to get contents of a FILE_NAME field, but FILE_CONTENTS is like 'oracle.sql.CLOB@9b6a5f7f'. Is there a posibility to get CLOB value from an object?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More advanced example request

Composite nested table

Objects in nested tables

Using Nested Table in Java

Retrieving CLOB from object

More to Explore

PL/SQL demos

PL/SQL docs

Can I Pass a nested table to Java from a pl/sql procedure.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More advanced example request

Composite nested table

Objects in nested tables

Using Nested Table in Java

Retrieving CLOB from object

More to Explore

PL/SQL demos

PL/SQL docs