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
