0 Replies Latest reply on Dec 14, 2009 1:16 PM by Shobhit Gupta

    Calling stored procedure

    Shobhit Gupta Newbie
      Hi

      I am using Seam 2.1, Jboss Application Server 5.0 and EJB3
      This is the fist time I am using this technology stack.
      In my application, I want to call stored procedure which is resided in oracle database.
      I have taken following steps to do so:

      In my entity class, I have added the following code:

      @NamedNativeQuery(name="baz", query="{call findVendor(?,:i)}", callable=true, readOnly=true, resultClass=PcProducts.class)

      where findVendor is my stored procedure and i is the input parameter.

      Stored procedure is as follows:

      create or replace procedure 
          findVendor(res out sys_refcursor, str in string) 
         as 
         begin 
             open res for 
                 select u.* from PC_PRODUCTS u where u.SKU_ID = str; 
          end findVendor;


      In my java class I am calling stored procedure in the following way:

      System.out.println("Calling SP ");
      PcProducts result = (PcProducts)em.createNamedQuery("baz").setParameter("i", "Sku1").getSingleResult();
                

      With the execution of the above code I am getting “missing right parenthesis” error as:

      17:25:58,046 INFO  [STDOUT] Calling SP
      17:25:58,093 INFO  [STDOUT] Hibernate:
          select
              *
          from
              ( {call findVendor(?,
              ?)} )
          where
              rownum <= ?
      17:25:58,265 WARN  [JDBCExceptionReporter] SQL Error: 907, SQLState: 42000
      17:25:58,265 ERROR [JDBCExceptionReporter] ORA-00907: missing right parenthesis

      17:25:58,312 ERROR [TxPolicy] javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not execute query
      17:25:58,359 ERROR [STDERR] Dec 14, 2009 5:25:58 PM javax.faces.event.MethodExpressionActionListener processAction

      Can anybody help me in locating where I am wrong; is this not the right way to call the stored procedure?
      Any help will me highly appreciated.

      Thanks in advance

      Shobhit