How is the webapp packaged? Does it contain the class
ejb/CallStoredProcedure? If it still doesn't work, can you attach a
test app?
-cheng
On 3/26/11 11:46 AM, Jose Alvarez de Lara wrote:
> Hi,
> I am trying to call an Oracle Stored Procedure using an EJB 3.1 injected
> in a Servlet. My environment is Eclipse Helios SR1 and GF 3.1.
> This is the log,
> [#|2011-03-26T16:08:33.937+0100|SEVERE|oracle-glassfish3.1|global|_ThreadID=22;_ThreadName=Thread-1;|Class
> [ Lejb/CallStoredProcedure; ] not found. Error while loading [ class
> controller.SPOutputServlet ]|#]
> [#|2011-03-26T16:08:33.953+0100|SEVERE|oracle-glassfish3.1|global|_ThreadID=22;_ThreadName=Thread-1;|Class
> [ Lejb/CallStoredProcedure; ] not found. Error while loading [ class
> controller.SPOutputServlet ]|#]
> I am basing in the Arun Gupta tutorial at,
> http://blog.arungupta.me/2009/09/totd-102-java-ee-6-servlet-3-0-and-ejb-3-1-wizards-in-eclipse/
> When I try an Eclipse Client Project with sayHello()
> example everything goes right. And in fact thats what I wanted to do
> but I get an exception when calling the more complicated stored
> procedure ejb. So I guess to develop a very simple servlet
> as the one in the tutorial.
> Here is my code,
> 1.- the ejb
> @Stateless
> @LocalBean
> public class CallStoredProcedure implements java.io.Serializable {
>
> private static final long serialVersionUID = 1L;
> /**
> * Default constructor.
> */
> public CallStoredProcedure() {
> // TODO Auto-generated constructor stub
> }
>
> @SuppressWarnings("unchecked")
> public List<Country> getCountriesSP(BigDecimal region)
> throws Exception {
> final Logger logger = Logger.getLogger("CallStoredProcedure");
>
> EntityManagerFactory emf =
> Persistence.createEntityManagerFactory("JPACallingOracleStoredProcedure-jpa");
> EntityManager em = emf.createEntityManager();
>
> try {
> EntityTransaction entr = em.getTransaction();
> entr.begin();
> JpaEntityManager jpaEntityManager = JpaHelper.getEntityManager(em);
> Session session = jpaEntityManager.getActiveSession();
> StoredProcedureCall call = new StoredProcedureCall();
> call.setProcedureName("HR.RECORD_SET.COUNTRIES_LIST");
> call.addNamedArgumentValue("P_REGION", region);
> call.useNamedCursorOutputAsResultSet("P_RESULTSET");
>
> ObjectLevelReadQuery query = new ReadAllQuery(Country.class);
> query.addArgument("P_REGION");
> query.setCall(call);
>
> List<BigDecimal> queryArgs = new ArrayList<BigDecimal>();
> queryArgs.add(region);
>
> return (List<Country>) session.executeQuery(query, queryArgs);
> } catch (Exception ex) {
> logger.severe("ERROR: " + ex.getMessage());
> throw ex;
> }
> finally {
> em.close();
> }
> }
> }
> 2.- the servlet
> @WebServlet(name="SPOutputServlet", urlPatterns={"/SPOutputServlet"})
> public class SPOutputServlet extends HttpServlet {
> private static final long serialVersionUID = 1L;
>
> @EJB CallStoredProcedure call;
> /**
> * @see HttpServlet#HttpServlet()
> */
> public SPOutputServlet() {
> super();
> // TODO Auto-generated constructor stub
> }
> /**
> * @see HttpServlet#doGet(HttpServletRequest request,
> HttpServletResponse response)
> */
> protected void doGet(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException {
> // TODO Auto-generated method stub
> doWork(request, response);
> }
> /**
> * @see HttpServlet#doPost(HttpServletRequest request,
> HttpServletResponse response)
> */
> protected void doPost(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException {
> // TODO Auto-generated method stub
> doWork(request, response);
> }
>
> protected void doWork(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException {
> // TODO Auto-generated method stub
> final Logger logger = Logger.getLogger("SPOutputServlet");
> ServletOutputStream out = response.getOutputStream();
>
> List<Country> list = new ArrayList<Country>();
> try {
> list = call.getCountriesSP(new BigDecimal(2L));
> } catch (Exception e) {
> // TODO Auto-generated catch block
> logger.severe("ERROR CALLING EJB: " + e.getMessage());
> }
>
> out.println("<html>");
> out.println("<head><title>SPOutputServlet</title></head>");
> out.println("<body>");
> out.println("<table align='center'>");
> for(Country c: list){
> out.println("<tr>");
> out.println("<td>" + c.getCountryName() + "</td>" + "<td>" +
> c.getCountryId() + "</td>");
> out.println("</tr>");
> }
> out.println("</table>");
> out.println("</body>");
> out.println("</html>");
> }
> }
> 3.- and the stored procedure,
> ...the specification
> CREATE OR REPLACE PACKAGE RECORD_SET
> AS
> type cursorType is ref cursor;
> Procedure COUNTRIES_LIST(p_region IN NUMBER, p_ResultSet out cursorType);
> END RECORD_SET ;
> ...the body
> CREATE OR REPLACE PACKAGE BODY RECORD_SET IS
> PROCEDURE COUNTRIES_LIST
> (
> p_region IN NUMBER,
> p_ResultSet out cursorType
> ) IS
> BEGIN
> OPEN p_ResultSet FOR
> SELECT *
> FROM countries
> WHERE region_id = p_region;
>
> EXCEPTION
> When NO_DATA_FOUND then
> dbms_output.put_line('No hay datos');
>
> END;
>
> END RECORD_SET ;
> /
> Any help or suggestion will be appreciated.
> Regards,
> Jose