Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ORA-01009: missing mandatory parameter

 
shawn sandy
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could anyone point me in the right direction to find this 'missing parameter',
Thanks in advance.
the error is --
Error: 500
Location: /ViewCatalog.jsp
Internal Servlet Error:
javax.servlet.ServletException: ORA-01009: missing mandatory parameter
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:459)
at _0002fViewCatalog_0002ejspViewCatalog_jsp_3._jspService(_0002fViewCatalog_0002ejspViewCatalog_jsp_3.java:146)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:797)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
Root cause:
java.sql.SQLException: ORA-01009: missing mandatory parameter
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1311)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:797)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:221)
at StoreBean.getCategories(StoreBean.java:127)
at _0002fViewCatalog_0002ejspViewCatalog_jsp_3._jspService(_0002fViewCatalog_0002ejspViewCatalog_jsp_3.java:88)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:797)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
The bad code is
public String[] getCategories() throws SQLException {
//Connection conn = connectionPool.getConnection();
us = myConn.createStatement();
rs = us.executeQuery(SELECT_CATEGORIES);
Vector v = new Vector();
// System.out.println("XXXXXXXXXXX BEFORE 0 XXXXXXXXXXX");
while (rs.next()) {
// System.out.println("XXXXXXXXXXX BEFORE 1 XXXXXXXXXXX");
String s = rs.getString(1);
// System.out.println("XXXXXXXXXXX BEFORE 2 XXXXXXXXXXX");
v.add(s);
// System.out.println("XXXXXXXXXXX BEFORE 3 XXXXXXXXXXX");
}
// System.out.println("XXXXXXXXXXX AFTER XXXXXXXXXXX");
//connectionPool.close(conn);
String[] sa = new String[v.size()];
for (int i=0; i<v.size(); ++i) {>
sa[i] = (String)v.elementAt(i);
}
return sa;
}
here is the string def --
SELECT_SUBCATEGORIES = "(SELECT DISTINCT cat FROM products)";
 
Julio Lopez
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Usually when I get this error it is because I am using a prepared statement and the number of parameters I set using prepStatement.setXXX does not correspond with the "?" in the SQL statement. It does not appear you are using a prepared statement but just wanted to tell you this is when I get it. Also, according to your code sample the SQL statement you are trying to execute in:
rs = us.executeQuery(SELECT_CATEGORIES);
is not the same as the SQL statement at the bottom
here is the string def --
SELECT_SUBCATEGORIES = "(SELECT DISTINCT cat FROM products)";
Julio Lopez
M-Group Systems
 
Rob Lyon
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is quite late, but I just happened to stumble across your question. The reason why you're getting that error is when you defined your query you used '(' and ')' to surround your query. Oracle JDBC drivers don't like that, even though it may work in other direct queries to the database from SQL *Plus or other oracle clients.
 
irundha van
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had the same problem When I did this...
String eSql = "";
if (..){
eSql = "select ....";
}
else id (..) {
eSql = "select....";
}
stmt.executeQuery(eSql);
----------
When all the above condition fails eSql = "" and not a valid query and I got that Mandatory error
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic