JDBC Program to retrieve Arrays from an Oracle Stored Procedure with only Array OUT Parameters

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();

  }

}

Quick Notes Finder Tags

Activities (1) advanced java (1) agile (3) App Servers (6) archived notes (2) Arrays (1) Best Practices (12) Best Practices (Design) (3) Best Practices (Java) (7) Best Practices (Java EE) (1) BigData (3) Chars & Encodings (6) coding problems (2) Collections (15) contests (3) Core Java (All) (55) course plan (2) Database (12) Design patterns (8) dev tools (3) downloads (2) eclipse (9) Essentials (1) examples (14) Exception (1) Exceptions (4) Exercise (1) exercises (6) Getting Started (18) Groovy (2) hadoop (4) hibernate (77) hibernate interview questions (6) History (1) Hot book (5) http monitoring (2) Inheritance (4) intellij (1) java 8 notes (4) Java 9 (1) Java Concepts (7) Java Core (9) java ee exercises (1) java ee interview questions (2) Java Elements (16) Java Environment (1) Java Features (4) java interview points (4) java interview questions (4) javajee initiatives (1) javajee thoughts (3) Java Performance (6) Java Programmer 1 (11) Java Programmer 2 (7) Javascript Frameworks (1) Java SE Professional (1) JPA 1 - Module (6) JPA 1 - Modules (1) JSP (1) Legacy Java (1) linked list (3) maven (1) Multithreading (16) NFR (1) No SQL (1) Object Oriented (9) OCPJP (4) OCPWCD (1) OOAD (3) Operators (4) Overloading (2) Overriding (2) Overviews (1) policies (1) programming (1) Quartz Scheduler (1) Quizzes (17) RabbitMQ (1) references (2) restful web service (3) Searching (1) security (10) Servlets (8) Servlets and JSP (31) Site Usage Guidelines (1) Sorting (1) source code management (1) spring (4) spring boot (3) Spring Examples (1) Spring Features (1) spring jpa (1) Stack (1) Streams & IO (3) Strings (11) SW Developer Tools (2) testing (1) troubleshooting (1) user interface (1) vxml (8) web services (1) Web Technologies (1) Web Technology Books (1) youtube (1)