How to automatically generate JDBC code for reading data from REF CURSOR inside Oracle's stored procedure

   Sometimes we have to write JDBC code which reads  block of data (record set) from oracle procedure.  Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Ref cursor can return more or less data fields inside each row.

For example,  the following procedure opens a query using a SYS_REFCURSOR  as output parameter:

CREATE OR REPLACE
PROCEDURE get_data_rs (p_cur OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cur FOR
       SELECT bank,
                       name,
                       date,
                       address
       FROM some_table;
END get_data_rs;

To collect data, your  JDBC code should be something like this:

   Connection conn = DriverManager.getConnection (" some connectionURL ...");
   CallableStatement cs= conn.prepareCall("{call get_data_rs (?)}");
   cs.registerOutParameter(1, OracleTypes.CURSOR); //REF CURSOR
   cs.execute();

  ResultSet rs = cs.getCursor(1);

  while (rs.next()) {
            //  Reading ref cursor values
           // *** This code  can be automatically  generated
  }
   rs.close();
   rs = null;
   cs.close();
   cs= null;
   conn.close();
   conn = null;

For each value inside recordset a getXXX() method (e.g., getInt(), getString()) is called.

Hand coding this is very error prone and time consuming task, especially if there are a large number of fields inside ref cursor or there are several PL/SQL procedures  to be called.

The idea is to make script which automatically generate appropriate reading JDBC code knowing parameters name and type in ref cursor.

First step is to create file that contains parameters names and their types.

bank  NUMBER
nameVARCHAR2
date  DATE
address VARCHAR2

File is named: output.txt and saved somewhere on file system

Second, write java program which reads file and creates desired output -  JDBC code which reads  ref cursor data from oracle procedure.

The program is listed below:

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Scanner;

public class OutputJDBC {
 /**
  * @param args
  */
 public static void main(String[] args) {
  // Location of file to read
  File file = new File(
    "C:\\output.txt");
  ArrayList<String> var2 = new ArrayList<String>();
  try {
   Scanner scanner = new Scanner(file);
   while (scanner.hasNextLine()) {
    String line = scanner.nextLine();
    System.out.println(line);
    var2.add(line);
   }
   scanner.close();
   System.out.println("**************************************");
   generateOutput(var2);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  }
 }
 public static void generateOutput(ArrayList<String> variables) {
  String str[] = null;
  for (String s : variables) {
   if (s.length() == 0)
    continue;
   str = s.split(" ");
   if (str[str.length - 1].trim().startsWith("VARCHAR2")) {
    System.out.println("rs.getString(\"" + str[0].trim() + "\")");
   }
   if (str[str.length - 1].trim().startsWith("NUMBER")) {
    System.out.println("rs.getInt(\"" + str[0].trim() + "\")");
   }
   if (str[str.length - 1].trim().startsWith("DATE")) {
    System.out.println("rs.getDate(\"" + str[0].trim() + "\")");
   }
  }
 }
}

Third, run program above and copy and paste generated output to your application (on the place with ***), it looks like:

rs.getInt("bank")
rs.getString("name")
rs.getDate("date")
rs.getString("address")

Fourth, fill your application data with results from cursor.

That is all. We have generated jdbc code which reads  ref cursor from oracle pl/sql (stored) procedure.