This tutorial guides us on how to pass Array objects from Java to stored procedures in Oracle and also, how to retrieve an array object in Java.
All PLSQL arrays can not be called from java. An array needs to be created as
TYPE, at SCHEMA level in the database and then it can be used with ArrayDescriptor in Java, as
oracle.sql.ArrayDescriptor class in Java can not access at package level.
First, Create an array, at SCHEMA level. An example is shown below:
Next, Create a procedure which takes an array as an input parameter and returns an array as its OUT parameter.
An example of one such procedure is shown below, which has 2 parameters –
- an array of String as its IN parameter – p_array
- an array of Integers as OUT parameter – p_arr_int
After this, Execution permission would be required to execute the procedure created by you:
Create a java class which makes a call to the procedure
proc1, created before.
Below is an example which contains the whole flow from creating a connection with the database, to making a call to the stored procedure, passing an array to Oracle procedure, retrieving an array from an Oracle procedure and displaying the result.
Class.forName()– Returns the Class object associated with the class or interface with the given string name.
DriverManager.getConnection()– Attempts to establish a connection to the given database URL.
oracle.sql.ArrayDescriptor– Describes an array class
ArrayDescriptor.createDescriptor()– Descriptor factory. Lookup the name in the database, and determine the characteristics of this array.
oracle.sql.ARRAY– An Oracle implementation for generic JDBC Array interface.
CallableStatement– The interface used to execute SQL stored procedures.