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.

How to automatically generate JDBC code for calling Oracle's stored procedure or function

Imagine you have oracle PL/SQL (stored) procedure (or function) and want to call it from within java program, you have to write some JDBC code to achieve it.  To do this, following  information are necessary:

1. Procedure (function) name
2. Number of parameters (input and output)
3. Parameters names
4. Parameters types


Your code should be something like this:

Connection con = DriverManager.getConnection (" some connectionURL ...");
CallableStatement cs= conn.prepareCall("{call ProcName(?,?,?)}");

cs.setXXX(name1, value); // *** This code can be automatically generated
cs.setXXX(name2, value);
cs.setXXX(name3, value);

cs.execute();

Let's say you want to call procedure containing input parameters eg. send data from within  your java application to oracle procedure. Handling procedure with output parameters can be achieved on similar way with some minor changes in the code.

Send data from java application to oracle pl/sql procedure

The  procedure's input parameters are bound to CallableStatement object using  setXXX() methods on the object.
For each input bind parameter, a setXXX() method (e.g., setInt(), setString(),) is called.

cs.setXXX(name, value);

name - procedure's parameter name
value - strong type value

Now, you can write this method for every parameter by hand, step by step. Of course, it is possible, but just imagine you have 20 or 30 + parameters in your oracle procedure or dozen of  procedures you must call within your application. Or both of above.
In one of my projects I had 40 + procedures to be called and some of them had more than 20 parameters.
Hand coding this is very error prone and time consuming task.

The idea is to make script which automatically generate appropriate JDBC code knowing stored procedure's signature.

Let's say you have following oracle procedure signature:

PROCEDURE  input_proc
       (p_bank              IN NUMBER
        p_name             IN VARCHAR2
        p_date               IN DATE)

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

p_bank IN NUMBER,
p_name IN VARCHAR2,
p_date IN DATE

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

Second, write java program which reads file and creates desired output - calling JDBC code.
The program is listed below:

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


public class InputJDBC {
 public static void main(String[] args) {
  // Location of file to read
  File file = new File("C:\\input.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("IN");
   if (str[1].replace(',', ' ').trim().startsWith("VARCHAR2")) {
    System.out.println("cs.setString(\"" + str[0].trim()
    + "\", null);");
   }
   if (str[1].replace(',', ' ').trim().startsWith("NUMBER")) {
    System.out.println("cs.setInt(\"" + str[0].trim()
      + "\", null);");
   }
   if (str[1].replace(',', ' ').trim().startsWith("DATE")) {
    System.out.println("cs.setDate(\"" + str[0].trim()
    + "\", null);");
   }
  }
 }
}


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

cs.setInt("p_bank", null);
cs.setString("p_name", null);
cs.setDate("p_date", null);

Fourth, put your desired data values instead of null inside the setXXX() methods.

That is all. We have generated jdbc code for calling PL/SQL procedure.