如何使用EclipseLink调用函数 [英] How to call function using EclipseLink

问题描述

如何使用EclipseLink调用返回sys_refcursor的Oracle函数?

How to call an Oracle function which returns sys_refcursor using EclipseLink?

有一个文档说明了有关调用函数的信息，但不确定如何调用返回sys_refcursor的函数.

There is a documentation which states about calling a function, but not sure how to call a function which returns sys_refcursor.

http://eclipse.org/eclipselink/documentation/2.4 /jpa/extensions/a_namedstoredfunctionquery.htm

我尝试如下

@NamedStoredFunctionQuery(name = "findEmployees", 
                          functionName = "getEmps", 
                          parameters = 
                          { @StoredProcedureParameter(queryParameter = "user", 
                                                      name = "username", 
                                                      direction = Direction.IN, 
                                                      type = String.class)
            } , 
    returnParameter = @StoredProcedureParameter(queryParameter = "c_cursor")
)

Oracle函数

CREATE or REPLACE FUNCTION getEmps (username varchar2)
      RETURN SYS_REFCURSOR
   AS
   c_cursor   SYS_REFCURSOR;
   BEGIN
   OPEN c_cursor FOR 
   SELECT * FROM employees where emp_no=username;
   RETURN c_cursor;

但是当我执行时，出现以下错误

However when I execute, I am getting the following errors

内部异常:java.sql.SQLException:ORA-06550:第1行，第13列: PLS-00382:表达式的类型错误 ORA-06550:第1行，第7列: PL/SQL:语句被忽略

Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored

错误代码:6550 致电:开始? := getEmps(用户名=>?);结尾; 绑定=> [=> c_cursor，S7845] 查询:DataReadQuery(name ="findEmps") 在org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378) 在org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260) 在org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)

Error Code: 6550 Call: BEGIN ? := getEmps(username=>?); END; bind => [=> c_cursor, S7845] Query: DataReadQuery(name="findEmps" ) at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)

如何解决此问题?

推荐答案

我认为您必须指定函数参数的Direction

I think you must specify the Direction of the functions parmeter

CREATE or REPLACE FUNCTION getEmps (username IN varchar2)
      RETURN SYS_REFCURSOR
   AS
   c_cursor   SYS_REFCURSOR;
   BEGIN
   OPEN c_cursor FOR 
   SELECT * FROM employees where emp_no=username;
   RETURN c_cursor;

请尝试！

这篇关于如何使用EclipseLink调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！