JPA PLS-00306：调用“STP_REFRESH_CATEGORY_GUARANTEE”的参数数量或类型错误

Question

我正在使用JPA调用oracle存储过程并获得下面提到的异常

[4/24/14 9:07:37:583 EDT] 00000031 SystemOut O [EL Warning]: 2014-04-24 09:07:37.452--UnitOfWork(1697919964)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STP_REFRESH_CATEGORY_GUARANTEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN STP_REFRESH_CATEGORY_GUARANTEE(SHIP_CODE=>?, SAIL_DATE=>?, return_code=>?); END;
    bind => [3 parameters bound]
Query: DataReadQuery(name="refreshCategoryGuarantee" )
[4/24/14 9:07:37:584 EDT] 00000031 SystemOut                                                    O         **********************************************************************************
[4/24/14 9:07:37:584 EDT] 00000031 SystemOut                                                    O EXCEPTION: Exception       [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STP_REFRESH_CATEGORY_GUARANTEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN STP_REFRESH_CATEGORY_GUARANTEE(SHIP_CODE=>?, SAIL_DATE=>?, return_code=>?); END;
    bind => [3 parameters bound]
Query: DataReadQuery(name="refreshCategoryGuarantee" ) ship_code: AL sail_date: 01-JUN-14

    try{
                Query query = getEntityManager().createNamedQuery("refreshCategoryGuarantee");
                query.setParameter("SHIP_CODE", ship_code);
                query.setParameter("SAIL_DATE", DateUtil.getSqlDate(sailDate));
                return (Integer) query.getSingleResult();
            }catch(Exception ex){
                System.out.println("**********************************************************************************");
                logger.error("Error executing refreshCategoryGuarantee named query with param["+ship_code+","+sailDate+"] ", ex);
                return StoredProcedureConstants.RETURN_CODE_EXCEPTION;
           }
    

Here, refreshCategoryGuarantee is named query of STP_REFRESH_CATEGORY_GUARANTEE procedures.

-------------
<pre>
@NamedStoredProcedureQuery(  
        name="refreshCategoryGuarantee",  
        procedureName="STP_REFRESH_CATEGORY_GUARANTEE",  
        returnsResultSet=false,  
        parameters={  
            @StoredProcedureParameter(queryParameter="SHIP_CODE", type=String.class),  
            @StoredProcedureParameter(queryParameter="SAIL_DATE", type=Date.class),  
            @StoredProcedureParameter(queryParameter="return_code", direction=Direction.OUT, type=Integer.class) 
        })

我的问题是，当我在oracle中执行下面提到的匿名块时，它的工作没有任何问题

    SET serveroutput ON
    DECLARE
      rc NUMBER;
    BEGIN
      STP_REFRESH_CATEGORY_GUARANTEE('OA','24-jan-15',rc);
      dbms_output.put_line('Response Code :'||rc);
    END; 

我不知道为什么我在JPA调用中得到这个？你能帮我解决它吗？

Answer 1

这与获取参数有关。不是通过参数名。

1-获得对EntityManager的访问权限。例如，通过@PeristenceContext私有EntityManager em；

2 -使用数字访问参数。这只是一个例子。

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(
    1,Long.class, ParameterMode.IN
)
.registerStoredProcedureParameter(
    2,Long.class, ParameterMode.OUT
)
.setParameter(1, 1L);
query.execute();
Long commentCount = (Long) query.getOutputParameterValue(2);

Answer 2

StoredProcedureParameter定义中的参数SAIL_DATE的类型，是java.util.date吗？它必须是java.sql.date...

@StoredProcedureParameter(queryParameter="SAIL_DATE", type=java.slq.Date.class)