How to pass an array of strings as an argument to a stored procedure during provisioning in an Oracle JDBC application

[#1]

This post has a sample stored procedure to understand the procedure of passing an array as an argument while calling a stored procedure both using a JAVA Client and a sample test rule. This is useful in case you want to send multiple records in a column instead of sending a String with delimiters.

Prerequisites

-------------------

ojdbc6 Jar File

This is a sample Stored Procedure that has an array as an input argument and an output len that returns the length of the array.  

Structure

--------------

create or replace PACKAGE idmUserpkg IS

   type userRoleArr IS VARRAY(2000) OF VARCHAR2(2000);

   PROCEDURE idmUserProvision

  (

    idmUserName Varchar,

    corUserName varchar,

    userRole IN userRoleArr,

    len OUT NUMBER

   );

END idmUserpkg;

Definition

------------

create or replace PACKAGE BODY idmUserpkg IS

   PROCEDURE idmUserProvision

   (

    idmUserName Varchar,

    corUserName varchar,

    userRole IN userRoleArr,

    len OUT NUMBER

   )

   AS

   BEGIN

      len:= userRole.COUNT;

      FOR i IN userRole.first .. userRole.last

      LOOP

         dbms_output.put_line(userRole(i));

      END LOOP;

   END;

END idmUserpkg;

Run both the Structure and Definition separately and complied them in the Oracle database. Once that is done then call the Stored Procedure from Java Client by passing an array as an argument to the SP and also from the sample test rule in IIQ mentioned below.

Java Client Code

-----------------------

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

import java.util.ArrayList;

import java.util.List;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

public class JDBCArraySP {

  public static void passAndGetArray() {

  try {

  Class.forName("oracle.jdbc.OracleDriver");

  Connection con = DriverManager.getConnection("jdbc:oracle:thin:username/password@host:port/servicename");

  List list = new ArrayList();

  list.add("1");

  list.add("2");

  list.add("3");

  ArrayDescriptor des = ArrayDescriptor.createDescriptor("IDMUSERPKG.USERROLEARR", con);

  ARRAY array_to_pass = new ARRAY(des, con, list.toArray());

  CallableStatement st = con.prepareCall("call idmUserpkg.idmUserProvision(?,?,?,?)");

  // Passing an array to the stored procedure

  st.setString(1, "test1");

  st.setString(2, "test1");

  st.setArray(3, array_to_pass);

  st.registerOutParameter(4, Types.INTEGER);

  st.execute();

  System.out.println("Output array size : " + st.getInt(4));

  if (st.getInt(4) > 0) {

  System.out.println("Array elements are present");

  }

  } catch (Exception e) {

  System.out.println(e);

  }

  }

  public static void main(String args[]) {

  passAndGetArray();

  }

}

IIQ Sample Test Rule

-----------------------------

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">

<Rule created="1460473585616" id="8a50231b52c6acaa01540b025fd01110" language="beanshell" modified="1460474693852" name="Test JDBC Stored Procedure Rule">

  <Source>

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

try {

  Class.forName("oracle.jdbc.OracleDriver");

  Connection con = DriverManager.getConnection("jdbc:oracle:thin:username/password@host:port/servicename");

  List list = new ArrayList();

  list.add("1");

  list.add("2");

  list.add("3");

  ArrayDescriptor des = ArrayDescriptor.createDescriptor("IDMUSERPKG.USERROLEARR", con);

  ARRAY array_to_pass = new ARRAY(des, con, list.toArray());

  CallableStatement st = con.prepareCall("call idmUserpkg.idmUserProvision(?,?,?,?)");

  st.setString(1, "test1");

  st.setString(2, "test1");

  st.setArray(3, array_to_pass);

  st.registerOutParameter(4, Types.INTEGER);

  st.execute();

  System.out.println("Output array size : " + st.getInt(4));

  if (st.getInt(4) > 0) {

  System.out.println("Array elements are present");

  }

   } catch (Exception e) {

  log.error("error occured " , e);

  }

    </Source>

</Rule>

Output in both the Cases

-----------------------------------

Output array size : 3

Array elements are present

Hope this is helpful and will help someone who is looking for a similar kind of requirement.

Thanks

Sumit Gupta