JDBC Program to Insert and Retrieve Arrays into and from an Oracle Stored Procedure

First we will create an array (nested table array or varray), use it in a stored procedure as IN and/or OUT parameters 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 Array IN and OUT parameters

CREATE OR REPLACE PROCEDURE procarrayinout (p_array     IN     array_table,

                                              len            OUT NUMBER,

                                              p_arr_out      OUT array_table)

AS

BEGIN

   len := p_array.COUNT;

   p_arr_out := NEW array_table ();

   p_arr_out.EXTEND (len);

 

   FOR i IN 1 .. p_array.COUNT

   LOOP

      DBMS_OUTPUT.put_line (p_array (i));

      p_arr_out (i) := p_array (i);

   END LOOP;

END;

 

JDBC Program to insert and retrieve Arrays into and 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;

import oracle.sql.ArrayDescriptor;

 

public class JDBCArraySP {

  public static void passAndGetArray() {

    try {

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

      Connection con = DriverManager.getConnection(

          "jdbc:oracle:thin:@10.60.28.83:1521:ORA11", "hej01", "hej01");

 

      String array[] = { "one", "two", "three", "four" };

 

      ArrayDescriptor des = ArrayDescriptor

          .createDescriptor("ARRAY_TABLE", con);

      ARRAY array_to_pass = new ARRAY(des, con, array);

 

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

 

      // Passing an array to the stored procedure

      st.setArray(1, array_to_pass);

 

      st.registerOutParameter(2, Types.INTEGER);

      st.registerOutParameter(3, OracleTypes.ARRAY, "ARRAY_TABLE");

      st.execute();

 

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

 

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

        System.out.println("Array elements:");

      }

 

      // Retrieving array from the resultset after executing the stored

      // procedure

      ARRAY arr = ((OracleCallableStatement) st).getARRAY(3);

      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[]) {

    passAndGetArray();

  }

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)