Engineering Full Stack Apps with Java and JavaScript
First we will create an array (nested table array or varray), use it in a stored procedure as OUT parameter and then populate and retrieve data through a JDBC program.
Creating array
We can create a nested table array as:
CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);
Or
We can create a VARRAY as:
CREATE TYPE array_table AS VARRAY(5) OF VARCHAR2(50);
Note: In nested table array (AS TABLE OF), you don’t specify an upper bound whereas in VARRAY, you specify an upper bound. If you pass more elements to a VARRAY than specified, you will get an exception as: Exceeded maximum VARRAY limit.
Oracle Stored Procedure with only Array OUT parameters
CREATE OR REPLACE PROCEDURE procarrayout ( len OUT NUMBER,
p_arr_out OUT array_table)
AS
v_count NUMBER;
BEGIN
len := 5;
p_arr_out := NEW array_table ();
p_arr_out.EXTEND (len);
v_count := 0;
FOR i IN 1 .. len
LOOP
p_arr_out (i) := 'dummy data';
v_count := v_count + 1;
END LOOP;
END;
JDBC Program to retrieve Arrays from an Oracle Stored Procedure
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
public class JDBCArraySPOUT {
public static void passArray() {
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@<host>:<port>:<sid>", "hej01", "hej01");
CallableStatement st = con.prepareCall("call procarrayout(?,?)");
st.registerOutParameter(1, Types.INTEGER);
st.registerOutParameter(2, OracleTypes.ARRAY, "ARRAY_TABLE");
st.execute();
System.out.println("size : " + st.getInt(1));
// Retrieving array from the resultset after executing the stored
// procedure
ARRAY arr = ((OracleCallableStatement) st).getARRAY(2);
String[] recievedArray = (String[]) arr.getArray();
for (int i = 0; i < recievedArray.length; i++)
System.out.println(recievedArray[i]);
} catch (Exception e) {
System.out.println(e);
}
}
public static void main(String args[]) {
passArray();
}
}