JDBC - Calling an Oracle Stored Procedure with CURSOR output

This article explains how to call an Oracle Stored Procedure that returns a Cursor as an output parameter with Java using JDBC.


Things to note:
- JDBC begins it's index's at 1, not 0. I.e. The CallableStatement object's methods such as: getObject must have a parameter with a value of at least 1.
- Once the Connection or CallableStatement objects are closed, the ResultSet will be empty. This is because it only exists as long as the database connection is open. Ensure you take your results before these objects are destroyed.
- oracle.jdbc.OracleCallableStatement and java.sql.CallableStatement offer the same outcome in this example. Therefore, I have opted to use the java.sql library.
- The '?' within the prepareCall parameter is a place holder for a parameter. This is populated (in this example) using the registerOutParameter method, declaring it's expected type.


Oracle Stored Procedure

Code Snippet

1. CREATE OR REPLACE PROCEDURE PACKAGENAME.GET_ACCOUNTS
2. (
3.     l_cursor OUT sys_refcursor
4. )
5. AS
6. BEGIN
7.     OPEN l_cursor FOR SELECT ACCOUNT_NAME FROM Accounts ORDER BY ACCOUNT_NAME;
8. END GET_ACCOUNTS;

End of Code Snippet

Java Code

Code Snippet

1. // Establishes and returns a new connection to the database
2. private Connection GetConnection() throws SQLException
3. {
4.     // Load and register Oracle driver
5.     DriverManager.registerDriver(new OracleDriver());
6.    
7.     // Establish a connection
8.     return DriverManager.getConnection("jdbc:oracle:thin:@SERVER:PORT:SID", "USERNAME", "PASSWORD");
9. }
10.  
11.  
12. // Gets a collection of all accounts within the database
13. public Collection<Account> GetAccounts() throws SQLException
14. {  
15.     Connection conn = null;
16.     CallableStatement cstmt   = null;
17.     Collection<Account> accounts = new ArrayList<Account>();
18.  
19.     try
20.     {
21.         // Connect
22.         conn = this.GetConnection();
23.  
24.         // Get ResultSet
25.         cstmt = conn.prepareCall("{call PACKAGENAME.GET_ACCOUNTS(?)}");   // '?' is a template for our Cursor OUT parameter
26.         cstmt.registerOutParameter(1, OracleTypes.CURSOR);
27.         cstmt.execute();
28.  
29.         // Index starts at 1, so we want our first result, which is the Cursor
30.         ResultSet rs = (ResultSet) cstmt.getObject(1);
31.  
32.         // process results one row at a time
33.         while(rs.next())
34.         {
35.             Account acc = new Account(rs.getString(1)); // Account name is the first (and only) result in our Cursor.
36.             accounts.add(acc); // Add account to our collection of accounts to return
37.         }
38.     }
39.     catch (SQLException e)
40.     {
41.         System.err.println(e.getErrorCode() + e.getMessage());
42.     }
43.         catch(Exception e)
44.         {
45.             e.printStackTrace();
46.         }
47.     finally
48.     {
49.         conn.close();
50.         cstmt.close();
51.     }
52.    
53.     return accounts; // Return collection of accounts to caller
54. }
55.  
56.  
57.  
58. // Account POJO
59. public class Account
60. {
61.     private String accountName;
62.  
63.     public Account(String accName)
64.     {
65.         this.accountName = accName;
66.     }
67.    
68.     public String getAccountName() {
69.         return this.accountName;
70.     }
71.  
72.     public void setAccountName(String accountName) {
73.         this.accountName = accountName;
74.     }
75. }

End of Code Snippet