jpa使用输出游标调用存储过程 [英] jpa calling stored procedure with output cursor

问题描述

 创建或替换PROCEDURE stored_proc （ret_cursor OUT sys_refcursor，inputParam IN NUMBER）
  -  body 
 END stored_proc; 
 $ b @Entity 
 @NamedNativeQuery（name =callStoredProc，
 resultClass = Result.class，
 query ={calling stored_proc（？，：inputParam）} ，
 callable = true，
 readOnly = true 
）
 public class Result {
 //映射结果集params。 
} 
 
 //获取结果集的JPA代码
 List< Result> resultList = getEntityManager（）。createNamedQuery（callStoredProc）
 .setParameter（inputParam，inputParam）
 .getResultList（）; 
  

这一切都很好，但是，如果我尝试改变过程定义以将光标更改为
第二个参数，并在JPA代码中做相应的参数更改，它不起作用。我得到这个错误：

$ b

[4/30/12 11：42：30：505 CDT] 00000025 SystemErr R引起：java.sql.SQLException：ORA-06550：第1行，第7列：
PLS-00306：调用'stored_proc'时错误的参数或参数类型

 创建或替换PROCEDURE stored_proc（inputParam IN NUMBER，ret_cursor OUT sys_refcursor）
  

如果输出游标始终为使用JPA时存储过程中的第一个参数？是否有解决方法？

解决方案

Uday和Steve都对。

截至目前，OUT参数应该是第一个参数。

我们必须等到JPA 2.1发布才能获得对存储过程的支持。

你可以看看 JPA 2.1规范

如果您现在想要实现jpa 2.1，那么EclipseLink将在这场比赛中领先。
您可以在这里找到EclipseLink的JPA 2.1实现状态 - http：// wiki .eclipse.org / EclipseLink / Development / JPA_2.1
和这里的例子 - http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures

您也可以访问JPA 2.1的页面Arun Gupta在 https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft

I am trying to call an oracle stored procedure that returns an output cursor usign JPA as follows

  create or replace PROCEDURE stored_proc(ret_cursor OUT sys_refcursor, inputParam IN NUMBER)
   -- body 
  END stored_proc;

  @Entity
  @NamedNativeQuery(name = "callStoredProc",  
         resultClass = Result.class,  
         query = "{call stored_proc(?,:inputParam)}",  
        callable = true,
        readOnly=true
  )
  public class Result{
    // map the result set params.
  }  

  //JPA code to get result set 
  List<Result> resultList =  getEntityManager().createNamedQuery("callStoredProc")
                                        .setParameter("inputParam", inputParam)
                            .getResultList();

This all works good but, if I try to alter the procedure definition to change the cursor as second parameter and make corresponding parameter changes in JPA code as well , it doesn't work. I get the error

[4/30/12 11:42:30:505 CDT] 00000025 SystemErr R Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'stored_proc'

    create or replace PROCEDURE stored_proc(inputParam IN NUMBER,ret_cursor OUT sys_refcursor)

Should the output cursor be always the first parameter in the stored proc when using JPA? Is there a workaround for it?

解决方案

Both Uday and Steve are right.

As of now, the OUT parameter should be the first argument.

And we have to wait till the release of JPA 2.1 to get the support for stored procedures.

you can have look at the JPA 2.1 Spec here

If you want the jpa 2.1 implementation right now, EclipseLink are ahead in that race. you can find the JPA 2.1 implementation status by EclipseLink here - http://wiki.eclipse.org/EclipseLink/Development/JPA_2.1 and examples here - http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures

Also you may like to visit the page of JPA 2.1 highlights by Arun Gupta in https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft

这篇关于jpa使用输出游标调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！