My Oracle Support Banner

How to Return a Table of Records from a Java Stored Procedure? (Doc ID 727813.1)

Last updated on MAY 25, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Goal

 How to return a table of records from a java stored function / procedure?

If a Java Stored Procedure (JSP) is attempting to return a array of String array (String []) it can not be called directly from PL/SQL because currently Oracle does not have a type that maps to a Java String array (String []).

In order to implement a JSP that returns a String array as a parameter would be change the Java code from a String array into a type that Oracle supports. This is a fairly simple change, but if the Java object being called is obtained from a 3rd party vendor, modifying the code is not always feasible which is what this Note will address.

Based on the Oracle� Database JDBC Developer's Guide and Reference - 10g Release 2 (10.2) documentation there is a section listing the supported datatypes. Within the datatype mapping table there is nothing for String arrays. The closest would be to use a java.sql.Array type or oracle.sql.ARRAY that maps to an Oracle Collection.

For this example, a Object type with three columns of VARCHAR2 is created and used to create an oracle.sql.ARRAY from the String array which is one of the supported types within PL/SQL. The Java class will be created to returns a oracle.sql.ARRAY type. Within the Java, the String array will be converted to the oracle.sql.ARRAY type and returned to the underlying function. Essentially the Java class is a "converter" that converts the Java type into the supported Oracle type.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
  
 Requirements
 Configuring
 Instructions
 Sample Code
 Sample Output
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.