Skip to Main Content
  • Questions
  • return resultset as an array from function

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Lara.

Asked: June 20, 2000 - 10:10 pm UTC

Last updated: February 11, 2008 - 10:12 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

I want to return a resultset from a function. I saw your example on how to return it as a ref-cursor, but I need to return it as an array and then retreive the array in Java. Do you have an example of this?

Thanks,
Lara


and Tom said...

Well, jdbc can deal with result sets from stored procedures -- it would be easiest to do the result set in virtually EVERY case. You can do it as an Array, you must use Oracle8i object types to do this.

The java code to retrieve an array can look like this (comments contain the sql you need to run before executing the java)

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

// create or replace type SimpleArray
// as table of varchar2(30)
// /
//
// create or replace function getSimpleArray
// return SimpleArray
// as
// l_data simpleArray := simpleArray();
// begin
// for i in 1 .. 10 loop
// l_data.extend;
// l_data(l_data.count) := 'entry ' || i;
// end loop;
// return l_data;
// end;
// /

class Array
{

public static void main(String args[]) throws Exception
{

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@aria:1521:ora8i",
"scott",
"tiger");

OracleCallableStatement stmt =
(OracleCallableStatement)conn.prepareCall
( "begin ? := getSimpleArray; end;" );

stmt.registerOutParameter( 1,
OracleTypes.ARRAY,
"SIMPLEARRAY" );
stmt.executeUpdate();

ARRAY simpleArray = stmt.getARRAY(1);

System.out.println ("Array is of type " +
simpleArray.getSQLTypeName());

System.out.println ("Array element is of type code " +
simpleArray.getBaseType());

System.out.println ("Array is of length " +
simpleArray.length());


String[] values = (String[])simpleArray.getArray();

for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" +
values[i] + "'" );

stmt.close();
conn.close();
}
}


that routine for example prints out this:


$ java Array
Array is of type SCOTT.SIMPLEARRAY
Array element is of type code 1
Array is of length 10
row 0 = 'entry 1'
row 1 = 'entry 2'
row 2 = 'entry 3'
row 3 = 'entry 4'
row 4 = 'entry 5'
row 5 = 'entry 6'
row 6 = 'entry 7'
row 7 = 'entry 8'
row 8 = 'entry 9'
row 9 = 'entry 10'

For additional information on this feature, please see

</code> http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/oraext7.htm#1040060 <code>

which is the jdbc manual, section on "Working with Arrays"

Rating

  (18 ratings)

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

Comments

Nice!

Mihai Manuta, April 04, 2001 - 9:30 am UTC

It’s one of the most unanswered questions in the forums. Unfortunately your answer raised another question: when I do the following:

cstmt=(OracleCallableStatement)conn.prepareCall("{call test(?,?)}");

cstmt.setObject(1,new String("ceva"),java.sql.Types.VARCHAR);
cstmt.registerOutParameter((2,OracleTypes.ARRAY,"PKG_DATATYPES.TV20");

cstmt.execute();

I get an exception:
Fail to construct descriptor: Unable to resolve type: "PKG_DATATYPES.TV20"
The PKG_DATATYPES.TV20 type is declared in the database (but wasn’t declared by me). Can you help me on that?


Wai, May 27, 2001 - 10:23 pm UTC


array passwing between java client & stored procedure

Wai, May 27, 2001 - 10:25 pm UTC


The example is good because the
code is simple and it worked (for 8.1.x only).

Unfortunately, when I apply the sample code to
8.0.4 database. I got the similar
result as "Mihai Manuta from Paris" have
(unable to resolve type).
Then I downloaded the most recent
Oracle thin client driver (use 8.1.7
driver conntecting to 8.0.4 database),
the program overcome the resolve type problem.
However it introduce another problem againt,
the result is in Hexdecimal format instead of
ASCII string!!!

Is there any special setting require at
both database server/java client level?


How ARRAY type can be used using 8.0.5 JDBC driver?

Avijit Saha, July 16, 2001 - 7:16 am UTC

Without changing JDBC 8.0.x driver how can I use ARRAY type? Now I'm working with Oracle client version 8.0.6 and JDBC version is 8.0.5 ( e.g. DatabaseMetaData meta = conn.getMetaData ();
System.out.println("JDBC driver version is " + meta.getDriverVersion()); ). Without using 8.1.x JDBC driver how can I get PL/SQL table as ARRAY type? Can any body help me?

A reader, July 23, 2002 - 8:46 am UTC

The array in the example is a simple array of table type varchar2(). what about a complex array..say String[][]
How would you go about it.


Tom Kyte
July 23, 2002 - 10:46 am UTC

two dimensional arrays are not supported until 9i (and string[][] is a 2 dim array) then they would map to a complex object type and you would use jpub to create a mapping class cause the code gets really ugly.

Read about jpublisher.

A reader, July 24, 2002 - 12:19 am UTC

Does that mean 2 Dim arrays cannot be passed by a java stored proc/function in 8i. Is there a work around.

Tom Kyte
July 24, 2002 - 9:55 pm UTC

since everything has to go through the SQL layer and in 8i there is no such thing as an array of arrays -- yes, you are correct, there is no way to do it.

You must pass single dimensioned arrays OR use remote procedure calls (RMI over IIOP).


see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/java.817/a83728/04jserv2.htm#1007944 <code>

(but not recommended)


ossie j, July 26, 2002 - 4:12 am UTC

so if 9i supports double dimensional arrays, is there any documentation or any sample program which demonstrates this, particularly in java egsample.

Tom Kyte
July 26, 2002 - 6:48 am UTC

yes, the plsql guide shows collections of collections and you'll want to use jpublisher to create the java mapping class.

How to Receive Arrays from Java in PL/SQL

Chandra S.Reddy, February 07, 2003 - 9:02 am UTC

Hi Tom,
I have one oustanding issue reg passing this arrays from Java to PL/SQL.

Am using Weblogic and Tomcat as application servers.So we obviously their(WL or Tomcat) connection pools.But not Oracle pool.
Plese look at the code below.
drop table t1 ;

create table t1 (a number(1), b varchar2(5));

drop type Num_List ;
create TYPE Num_List as TABLE OF NUMBER; -- create array of number
/

CREATE OR REPLACE PROCEDURE SP_PoC_Test_Array (in_val Num_List) IS
BEGIN
FORALL i IN in_val.FIRST..in_val.LAST
INSERT INTO T1 (A) VALUES (in_val(i)) ;
END SP_PoC_Test_Array;
/
-- Java program.
import java.sql.*; import oracle.jdbc.driver.*; import oracle.sql.*;import oracle.jdbc.oracore.Util;import java.math.BigDecimal;

public class ArrayTest1
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{

String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@spindc4:1521:java1";

Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "chandra", "chandra");

CallableStatement cstmt = conn.prepareCall("{CALL sp_cmsa_test_array(?)}");
int elements[] = { 1, 2, 3, 4, 5 };
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_LIST", conn);
ARRAY newArray = new ARRAY(desc, conn, elements);
((OraclePreparedStatement)cstmt).setARRAY(1, newArray);
cstmt.execute();
cstmt.close();

}
}

The above implementations are working fine, when we tested as a standalone programs.

But, while developing the application, we should use Weblogic connection or Tomcat connection. Not Oracle driver.
In the above code "ArrayDescriptor" object is Oracle's own implementation(correct if am wrong).
There actually we are landing into errors.

Is there anyway to overcome this.
Shall we do this Java SPs or any other kind.
Another restriction, we have is there should not be any SQL, PL/SQL work inside in Java.Everything must be inside Stored procs.

*******
Corrently we are implementing this issue like below.

TYPE t_Roles IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; -- declare collection element for Ids and Flags.
v_Roles t_Roles ;

v_Indx := 0;

LOOP
v_Indx := v_Indx+1;

v_Position := INSTR(v_AllRoles, '~'); -- find position of delimiter (tild character). if string has any value, at least one delimiter is always there with it.

EXIT WHEN v_Position = 0 ; -- exit loop when delimiter not found

v_Roles(v_Indx) := TO_NUMBER(SUBSTR(v_AllRoles, 1, v_Position - 1)) ; -- take string before the position of delimiter

v_AllRoles := SUBSTR(v_AllRoles, v_Position + 1) ; -- reassign remaining string to same variable

END LOOP ;
-- more code here---

Where "v_AllRoles" is an IN parameter. I get values for this from application with '~' as a delimeter(ex:- '1~2~3~4~0'). In PL/SQL I tokenize the string(v_AllRoles) and construct an array v_Roles as in the above code.
And use the the "v_Roles" to do other transactions.

This kind of IN strings(~ delimited strings)are 'n' in number some times. Then I need to add 'n' number of loops.Finally Code looks very ugly.

Please suggest us.



Tom Kyte
February 07, 2003 - 12:20 pm UTC

array's are proprietary -- if a database has them, they are implemented in their fashion.

jdbc is "open" -- meaning the spec only defines that which is at the lowest common denominator.

We have extensions in our jdbc driver to allow you to use all of our functionality.

They do not.

Now, that aside:

...But, while developing the application, we should use Weblogic connection or
Tomcat connection. Not Oracle driver....

is something I don't agree with -- you don't have to, you are free to use OUR driver if you like. No reason you shouldn't -- especially if you use this construct alot.

nested table of object type as parameter to proc

A reader, April 10, 2003 - 4:55 am UTC

if i have a nested table of an object type can i map that to java?
meaning:
create type test_obj as object
(col1 varchar2(10),col2 number,col3 number);

create type test_tbl as table of test_obj;

can java use this test_tbl for mapping?
or is this considered as a two dimensional array?

how do i handle such situaions?



Tom Kyte
April 10, 2003 - 8:05 am UTC

Yes you can -- jpublisher is useful for doing so as it writes the code for you.

</code> http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-JPU <code>


Returning a pl/sql table from a procedure and function

A reader, September 17, 2003 - 11:43 pm UTC

Returning a pl/sql table from a procedure and function, i.e. the solution you gave above in pl/sql

Can you give a simple example for the above.

returning a result set....The best way

Dan White, December 24, 2003 - 12:04 pm UTC

Tom,

From what I have read there are 2 possible ways of returning result sets from Oracle stored procedures to Java(ref cursors and Arrays)(oracle 8.1.7.4). We are having an enternal debate at my client about the best way to go about this. We need to return multiple columns of data with multiple rows. We (java and Oracle Developers) would like to use ref cursors to return data due to the dynamic nature of our application. The DBA's here would like us to use arrays(nested table) to pass information back to the java client. there reasoning is that if the connection to the webserver is lost then there will be an open cursor left. If a session is activly retrieving data from a webserver and the connection to that server is lost what will happen to the session?

Tom Kyte
December 24, 2003 - 1:35 pm UTC

there is only one way to return a result set -- ref cursor.

to return an array is to return an array (no result set semantics about it).

The DBA's would rather you use scads of memory? To be really inefficient. The only correct method is a ref cursor, really ( wrote about this in my new book Effective Oracle By Design and showed the reasons you don't want to consider the arrays -- its ugly ).


How would there be any open cursors? they are worried about something that isn't going to happen. Say the browser crashes -- the jsp or whatever finishes and wah-lah, gives up the connection and all cursors would be closed.

Say the connection from the app server to the db is lost. Well, thats what DCD (dead client detection) will handle, database will clean up.

A lost connection will not result in open cursors lying about (and even if they do, so what? cursors are session specific -- the session would be left open -- no one else would be able to get to that session -- THAT is the problem they want to focus on and it is a problem you have with both techniques.


The only way to do this is a ref cursor.

Tom Execelent answer

Dan White, December 29, 2003 - 1:15 pm UTC

Tom,

Thank you for the Answer, This is exactally what I have been trying to get across to our DBA staff.

Thank you
Dan

correct answer for Wai

xiewen, November 19, 2004 - 5:21 am UTC

/*
drop table varray_table;
drop type num_varray;

CREATE TYPE num_varray AS VARRAY(10) OF VARCHAR2(12)
/
CREATE TABLE varray_table (col1 num_varray);
INSERT INTO varray_table VALUES (num_varray('&#20320;&#22909;', 'abc'));

select * from varray_table;

*/

import java.sql.*;
import java.math.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class Array1
{

public static void main(String args[]) throws Exception
{
int oracleId = CharacterSet.ZHS16GBK_CHARSET;
CharacterSet dbCharset = CharacterSet.make(oracleId);

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@10.9.200.58:1521:db01",
"mytest",
"mytest");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");

while (rs.next()) {
ARRAY my_array = ((OracleResultSet)rs).getARRAY(1);

// return the SQL type names, integer codes,
// and lengths of the columns
System.out.println ("Array is of type " + my_array.getSQLTypeName());
System.out.println ("Array element is of typecode " + my_array.getBaseType());
System.out.println ("Array is of length " + my_array.length());

// get Array elements
String[] values = (String[]) my_array.getArray();
for (int i = 0; i < values.length; i++)
{
oracle.sql.CHAR out_value = new oracle.sql.CHAR(values[i], dbCharset);
System.out.println(">> index " + i + " = " + out_value);
}


}

rs.close();
stmt.close();
conn.close();
}
}


Resultset is passing to the Array

Ramesh Battineni, May 18, 2005 - 12:08 am UTC

Hello,
It's very usefull for me because I want to take the Resultset Data and store it in to an array, then that array need to paas in to the JTable Objects.

returning Incorrect type code

Anup Toshniwal, February 10, 2008 - 2:51 am UTC

Hi,

I have executed the same code ( passing Oracle Array to Java) as above using JDK 1.4 and Oracle 8i, but the java Array returns me type code 12 (varchar) instead of 1 (char), and this returns me values just like this (? ? ?) for every row of the Array. Not able to find out what they problem is and am not able to change the type code so that it returns the data in correct format. I suspect it is either the problem with either the JDBC library (ojdbc14.jar ) or the oracle 8i installation/options. We have also tested the above code with a 8i, jdk1.2, classes12.zip environment, but it still returns code 12.
Can you please guide as to what are the areas to be looked and how do we resolve for returning type code 1.

Regards,

Anup
Tom Kyte
February 11, 2008 - 10:12 pm UTC

well, that is what this code was first written with.

so, did you run *my* code or *your* code.

Perhaps your code was not correct code?

To: Anup Toshniwal re: returning Incorrect type code

Stew Ashton, February 12, 2008 - 2:36 am UTC


I'm not at work so I can't test this, but you might try using orai18n.jar in addition to ojdbc14.jar. It provides NLS support.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

haaseg, February 13, 2008 - 7:41 am UTC

We also started using the JDBC Array interface mapped by Oracle to object relational data.

This is a good solution to provide data into procedures.
But we changed to use the PL/SQL PIPE ROW machanism and sql table operator to read data from stored procedures.
See
http://www.psoug.org/reference/pipelined.html
for an example.
There are many advantages using PIPE ROW:

1. you can read the data via the standard JDBC/resultset interface without using oracle specific extensions.

2. You don't have to overload the procedure when attributes are added to the result and you want to be backward compatible.

3. it needs less resources for large arrays because it is piped.

4. Personal performance tests show that the time need is comparable to ref cursors (of cause the fastet solution) and the array interface.

orai18n.jar does the job for NLS support

Anup Toshniwal, February 14, 2008 - 10:14 am UTC

Hi everyone,

Thanks to everyone for their suggestions, but hearty thanks to Stew for his advise on the query raised by me. The code is really working fine now giving desired results after using orai18n.jar along with ojdbc14.jar. This has been tested with a jdk1.4 , oracle 8i environment on Unix, so should work fine with further release combinations.

Regards,

Anup

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library