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 10.1.0.2 to 10.2.0.5 [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.
Goal
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.
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 |