从Grails服务调用PLSQL存储过程 [英] Calling PLSQL stored proc from Grails service

问题描述

这需要一点帮助，也许还有一点。我试图从Grails服务中调用Oracle 11g中的存储过程。

到目前为止：

  import java.sql。* 
 import groovy.sql.Sql 
 import oracle.jdbc.driver.OracleTypes 
 $ b $ 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  - > 
 
 if（cursorResults.next（））{
 results = cursorResults.getAt（1）; 
} 
} 
 return resultList 
} 
  

好吧，所以这将返回第一行数据，并且取决于传递给getAt（）方法的内容，我可以获取该列。我在这里找到的 ORACLE存储过程在
< a>

我真正想要的是返回结果集并将其放入列表中，我只是不确定如何去做。

当我尝试 {cursorResults - > println cursorResults} 它会抛出一个错误

 消息：org.apache.commons.dbcp.DelegatingCallableStatement with地址：oracle.jdbc.driver.T4CCallableStatement @ ....已关闭
  

运行此过程直接在Oracle中有游标中有457行，如果有帮助的话。

编辑1：
响应dmahapatro，这是NPE

  |错误2013-05-07 14：16：05,123 [http-bio-8080-exec-1] ERROR errors.GrailsExceptionResolver  - 处理请求时发生NullPointerException：[GET] / testapp / messages / list 
 Stacktrace如下所示：
消息：null 
 Line |方法
  - >> 15 | testapp.MessagesController中的列表$$ EO5AzzAw 
  -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -  
 | 195 | doFilter in grails.plugin.cache.web.filter.PageFragmentCachingFilter 
 | 63 | doFilter in grails.plugin.cache.web.filter.AbstractFilter 
 | 886 | java.util.concurrent.ThreadPoolExecutor中的runTask $ Worker 
 | 908 |跑 。 。 in''
 ^ 662 |在java.lang.Thread中运行
  

现在第15行简单地是 println dummyService.listPeople（）

解决方案

sql.call 结果转换为 GroovyResultSet 。您应该可以对结果集执行 eachRow 并将其推送到列表中。

  def listPeople（）{
 sql sql = new groovy.sql.Sql（dataSource）
 def resultList = [] 
 
 sql.call（BEGIN mypackage .p_get_people（？）; END;，
 [Sql.resultSet（OracleTypes.CURSOR）]）{cursorResults  - > 
 cursorResults.eachRow {result  - > 
 resultList<<结果
} 
} 
返回resultList 
} 
  

编辑：

或者使用 sql.eachRow

  sql.eachRow（BEGIN mypackage.p_get_people（？）; END;，
 [Sql.resultSet（OracleTypes.CURSOR）]）{行 - > 
 resultList<<行
} 
  

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.

So far:

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 -> 

            if(cursorResults.next()) {
                results = cursorResults.getAt(1);
            }
    }
    return resultList
}

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 ORACLE STORED PROCS IN GRAILS

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.

When I try {cursorResults -> println cursorResults} it throws an error

Message: org.apache.commons.dbcp.DelegatingCallableStatement with Address: "oracle.jdbc.driver.T4CCallableStatement@...."is closed

Running this procedure directly in Oracle there is 457 rows in the cursor, if that helps at all.

Edit 1: Response to dmahapatro, this is the NPE

| 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
->>  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

And right now line 15 is simply println dummyService.listPeople()

解决方案

sql.call results to GroovyResultSet. You should be able to do an eachRow on the resultset and push it to the list.

def listPeople(){
    Sql sql = new groovy.sql.Sql(dataSource)
    def resultList = []

    sql.call("BEGIN mypackage.p_get_people(?); END;",
            [Sql.resultSet(OracleTypes.CURSOR)]) {cursorResults -> 
            cursorResults.eachRow{result ->
                resultList << result
            }
    }
    return resultList
}

EDIT:

Alternatively using sql.eachRow

    sql.eachRow("BEGIN mypackage.p_get_people(?); END;",
            [Sql.resultSet(OracleTypes.CURSOR)]) {row -> 
                resultList << row
    }

这篇关于从Grails服务调用PLSQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！