Tuesday, 17 July 2012

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.     OPEN l_cursor FOR SELECT ACCOUNT_NAME FROM Accounts ORDER BY ACCOUNT_NAME;
  6. 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

2 comments:

Anonymous said...

If a result set is returned and cast when using GetObject, it will probably be easier to change lines 27 to 30 to:

ResultSet rs = cstmt.executeQuery();

because executeQuery(); returns a ResultSet object.

Also, you're probably better explicitly calling the column name, e.g.:

rs.getString("ACCOUNT_NAME");

That way you'll be able to change the stored procedure without worrying about changing the index of the row you're after.

I'd also print e.getSQLState() in the catch.

Anonymous said...

Look this framework (https://code.google.com/p/lindbergframework/). Easy handling of stored procedures and functions, including treatment of cursors automatically.

https://code.google.com/p/lindbergframework/