Blog

Here is a piece of java code, which fetches data from SYS_REFCURSOR,

using the OracleTypes.CURSOR argument type:

import java.sql.*;
import java.util.Date;
import oracle.jdbc.OracleTypes;

public class ListCountries {

    static Driver driver;
    static Connection conn = null;
    static String driverUsed = "oracle.jdbc.driver.OracleDriver";

    static String serverAddress = "jdbc:oracle:thin:apps/apps@ora11g_wl:1521:ora11g";      

    public static void listEuropeanCountries(String svr) {
        try {
            System.out.println("Loading ... " + driverUsed);
            driver = (Driver) Class.forName(driverUsed).newInstance();
            System.out.println("Connecting ... " + svr);
            conn = DriverManager.getConnection(svr);            
            System.out.println("Ready.");
            Date startDate = new Date();
            
            String theQuery = "BEGIN xxtest_pkg.get_countries(continent=>?, country_list=>?); END;"; 
            System.out.println(theQuery);
            
            CallableStatement callStmt = conn.prepareCall(theQuery);
            callStmt.setString(1, "Europe");
            callStmt.registerOutParameter(2, OracleTypes.CURSOR);            
            callStmt.execute();

 
            //return the result set
            ResultSet rset = (ResultSet)callStmt.getObject(2);

            //find the number of the columns
            ResultSetMetaData rsetMeta = rset.getMetaData();
            int count_rows = rsetMeta.getColumnCount();
            int count_records = 0;
            // print the results, all the columns in each row
            while (rset.next()) {
                String rsetRow = "";
                for (int i=1; i<=count_rows; i++){
                       rsetRow = rsetRow + " " + rset.getString(i);
                }
                System.out.println(rsetRow);
                count_records++;
             }
            
            System.out.println("\n--------------");
            System.out.println(count_records + " records");
            Date endDate = new Date();
 
            System.out.println("in " + (endDate.getTime() - startDate.getTime()) + " milliseconds");
            conn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        listEuropeanCountries(serverAddress);
    }
}

The package which contains the called PL/SQL procedure get_countries is:

CREATE OR REPLACE PACKAGE APPS.XXTEST_PKG AS

  PROCEDURE get_countries(continent in varchar2, 
                            country_list out sys_refcursor);
                            
  PROCEDURE list_european_countries;                          
 
END XXTEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY APPS.XXTEST_PKG AS

  PROCEDURE get_countries(continent in varchar2, 
                            country_list out sys_refcursor) is
                    
      begin
      
         open country_list for
         Select    country_id, country_desc
         from xxsp_countries
         where 1=1
         and country_region=continent;

      end; 
  
  PROCEDURE list_european_countries is
                  
      l_country_list sys_refcursor; 
      
      l_country_id number(10,0);
      l_country_desc varchar2(100);
                  
      begin
         get_countries(continent=> 'Europe', 
                        country_list => l_country_list);
         
          --open l_country_list;  --> no, already open!!!  
          
          loop
              fetch l_country_list into l_country_id, l_country_desc;
              
              if (l_country_list%notfound) then
                  close l_country_list;
                  exit;
              end if;
              --or: exit when countries%notfound; 
              dbms_output.put_line(l_country_id || ' ' || l_country_desc);
          end loop;         
      end;                            

END XXTEST_PKG;
/

NOTE: The procedure list_european_countries in the package above, does exactly the same thing with java procedure listEuropeanCountries.

Database table: XXSP_COUNTRIES

PL/SQL call:

begin
    xxtest_pkg.list_european_countries;
end;

DBMS_OUTPUT:

Java System.out: