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/
[1] When I try an Eclipse Client Project with sayHello()
example everything goes right. And in fact that is 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
[1]
http://blog.arungupta.me/2009/09/totd-102-java-ee-6-servlet-3-0-and-ejb-3-1-wizards-in-eclipse/
--
[Message sent by forum member 'josealvarezdelara']
View Post: http://forums.java.net/node/785776