aspose file tools*
The moose likes JDBC and the fly likes ORA-01009: missing mandatory parameter Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01009: missing mandatory parameter" Watch "ORA-01009: missing mandatory parameter" New topic
Author

ORA-01009: missing mandatory parameter

shawn sandy
Greenhorn

Joined: Jul 02, 2001
Posts: 12
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

Joined: Nov 14, 2000
Posts: 28
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

Joined: Nov 29, 2001
Posts: 7
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

Joined: Jun 03, 2003
Posts: 1
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: ORA-01009: missing mandatory parameter