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