3 Replies Latest reply: Jul 17, 2012 12:47 PM by Cdelahun-Oracle RSS

    JPA with Oracle Stored Procedure: Missing IN or OUT parameter at index:: 1

    949087
      Hi There,

      I'm having trouble setting up integration between an Oracle Stored Procedure (which returns a cursor) and JPA.



      Stored Procedure
      CREATE OR REPLACE PROCEDURE GET_ACCOUNTS_TEST
      (
      l_cursor out sys_refcursor
      )
      AS
      BEGIN
      OPEN l_cursor FOR SELECT * FROM ACCOUNTS_TEST ORDER BY ACCOUNT_NAME;
      END GET_ACCOUNTS_TEST;



      Oracle Bean
      @Entity
      @NamedNativeQueries({
      @NamedNativeQuery(name = "getAccountsSP", query = "{call GET_ACCOUNTS_TEST(?)}", resultClass = Account.class)
      })
      @Table(name="ACCOUNTS_TEST")
      public class Account implements Serializable {
      .....
      }



      ManagedAccountBean
           public Collection<Account> getAccountsSP() {
                
      EntityManager em = jpaResourceBean.getEMF().createEntityManager();

      try {
           Query query = em.createNamedQuery("getAccountsSP");
      return (Collection<Account>) query.getResultList();
      } finally {
      em.close();
      }
      }



      Throws this error
      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 'GET_ACCOUNTS_TEST'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      Error Code: 6550
      Call: {call GET_ACCOUNTS_TEST(?)}
           bind => [null]
      Query: ReadAllQuery(name="getAccountsSP" referenceClass=Account sql="{call GET_ACCOUNTS_TEST(?)}")




      Any ideas? I can get the code to work with a query in the code, but not via. an AP

      THanks