title: Calling PLSQL stored proc from Grails service
body: <p>Need a little help with the closure part of this, and maybe a bit more. I'm trying to call a stored procedure in Oracle 11g from my Grails service.</p>
  
  <p>So far:</p>
  
  <pre><code>import java.sql.*
  import groovy.sql.Sql
  import oracle.jdbc.driver.OracleTypes
  
  class DummyService {
  
  def dataSource
  
  def serviceMethod() {
  }
  
  def listPeople(){
      Sql sql = new groovy.sql.Sql(dataSource)
      def resultList = []
  
      sql.call("BEGIN mypackage.p_get_people(?); END;",
              [Sql.resultSet(OracleTypes.CURSOR)]) {cursorResults -&gt; 
  
              if(cursorResults.next()) {
                  results = cursorResults.getAt(1);
              }
      }
      return resultList
  }
  </code></pre>
  
  <p>Alright, so this returns the first rows data, and depending on what is passed to the getAt() method, I can grab that column. Which I found here <a href="http://www.craigburke.com/post/44771719252/oracle-stored-procs-in-grails" rel="nofollow">ORACLE STORED PROCS IN GRAILS
  </a></p>
  
  <p>What I really want is to return the result set and put it into a list, I'm just not sure how to do it.</p>
  
  <p>When I try <code>{cursorResults -&gt; println cursorResults}</code> it throws an error</p>
  
  <pre><code>Message: org.apache.commons.dbcp.DelegatingCallableStatement with Address: "oracle.jdbc.driver.T4CCallableStatement@...."is closed
  </code></pre>
  
  <p>Running this procedure directly in Oracle there is 457 rows in the cursor, if that helps at all.</p>
  
  <p>Edit 1:
  Response to dmahapatro, this is the NPE</p>
  
  <pre><code>| Error 2013-05-07 14:16:05,123 [http-bio-8080-exec-1] ERROR errors.GrailsExceptionResolver  - NullPointerException occurred when processing request: [GET] /testapp/messages/list
  Stacktrace follows:
  Message: null
  Line | Method
  -&gt;&gt;  15 | list     in testapp.MessagesController$$EO5AzzAw
  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
  |   195 | doFilter in grails.plugin.cache.web.filter.PageFragmentCachingFilter
  |    63 | doFilter in grails.plugin.cache.web.filter.AbstractFilter
  |   886 | runTask  in java.util.concurrent.ThreadPoolExecutor$Worker
  |   908 | run . .  in     ''
  ^   662 | run      in java.lang.Thread
  </code></pre>
  
  <p>And right now line 15 is simply  <code>println dummyService.listPeople()</code> </p>