If you're using JPA with Jboss Seam will be:

  String sup_name ="Tortuga Trading";
        BigDecimal sum = (BigDecimal)em.createNativeQuery("SELECT sum_total(?1) FROM DUAL")
                      .setParameter(1, sup_name)
                      .getSingleResult(); 
        out.println("The total cost of the ordered products supplied by Tortuga Trading: " + sum +"<br/>");

Regards,

You should make a Google search on Hibernate/JPA + Stored Procedure.

Insightful and incredibly helpful. Always good to know there are people out there who truly cherish helping the fellow man.

Palani - here is an example that I have used:

'
Query query = entityManager.createNamedQuery(pkgname.fnname);
            query.setParameter(param1, param1);
            bean = (Bean) query.getSingleResult();'

The Bean is annotated like :

'@Entity
@NamedNativeQuery(name = pkgname.fnname, resultSetMapping = results, query = {? = call SCHEMANAME.pkgname.fnname(:param1)}, hints = {
    @QueryHint(name = org.hibernate.callable, value = true),
    @QueryHint(name = org.hibernate.readOnly, value = true) })
@SqlResultSetMapping(name = results, entities = { @EntityResult(entityClass = BeanName.class) })

'

@NamedNativeQuery(name="storedProc",
                  query="sp_get_department_utility(:param1, :param2)",
                  hints={
                              @QueryHint(value = "true", name="org.hibernate.callable"),
                              @QueryHint(value = "true", name="org.hibernate.readOnly")})
@SqlResultSetMapping(name="resul", entities={
          @EntityResult(entityClass = SysDepartments.class),
          @EntityResult(entityClass = Employee.class),
          @EntityResult(entityClass = ProductOrderLimitation.class),
          @EntityResult(entityClass = ProductOrderLimitation.class),
          @EntityResult(entityClass = ProductMaster.class)})

Query q=(Query) this.getEntityManager().createNamedQuery("storedProc")
          .setParameter(":category", ParamUtil.getCategory())
          .setParameter(":clientId", LoginUserInfo.getClientId());
          int list = q.executeUpdate();

It returns named query not found