My Oracle Support Banner

Calling a Java Stored Procedure that Contains a String Array With JDBC 10g (Doc ID 549634.1)

Last updated on MAY 25, 2023

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.1 to 10.2]
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 Backup Service - Version N/A and later
Information in this document applies to any platform.



If a Java Stored Procedure (JSP) has a String array (String []) as a parameter, it can not be called 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 accepts a String array as a parameter you would need to 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 that maps to an Oracle Collection.

For this example, a Nested Table of type VARCHAR2 is created and passed to a JSP that accepts a java.sql.Array type which is one of the supported types within PL/SQL. Assuming the original java class can't be modified, a new Java class will have to be created that accepts a java.sql.Array type. Once within this new Java class, the java.sql.Array type will be converted to the String array and passed to the underlying procedure. Essentially the new java class is a "converter" that converts the supported Oracle type into the Java type in order to call the underlying class.


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
 Sample Code
 Sample Output

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