ORA-01009: missing mandatory parameter

[shawn sandy]

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;
}

[DC Dalton]

Finally a question I can help on! This is an Oracle error. Obviously you are using an Oracle database to connect to. It looks to me that you are attempting to select all from some database here. Im referring to the SELECT_CATAGORIES in your executeQuery. Im not sure how your db is set up but selecting anything from a db (esp. Oracle) requires a select statement similar to this: "Select catagories [where catagories is the name of the column(s) you wish to select] from [table_name]" If you use a try catch block that catches the SqlException & then get-print Oracle error message life will be much simpler. Here's a quick example:
try {
//existing code here
}
catch (SQLException sqle) {
System.err.println(sqle.getMessage());
}
This will print the retrieved Oracle error to the console but Im pretty sure your query is incorrect. Check your SELECT_CATAGORIES String.......Hope this helps!
[This message has been edited by DC Dalton (edited July 02, 2001).]

[shawn sandy]

here is the string def --
SELECT_SUBCATEGORIES = "(SELECT DISTINCT cat FROM products)";

[DC Dalton]

Why do you have your string in parens? The String thats being fed to Oracle will include the () There are no parens in an Oracle select statement. The correct String should be
SELECT_SUBCATEGORIES = "Select DISTINCT cat FROM products";
Try that Im 99.99% sure that will eliminate that error

[shawn sandy]

removing the ()'s doesn't fix the problem. not sure why they are there, that is the example in the book though.

[Frank Carver]

This sounds very similar to an error I seem to recall getting a few months ago. Which Oracle database version and which driver driver version are you using? If you are still using a JDBC 1 driver (oracle111.zip ?) then I would recommend upgrading to a JDBC 1.2 version (oracle12_01.zip ?). If you are already using a 1.2 version, then make sure that all end-of-line characters in the SQL string have a space before them (!).
If neither of these fixes help, I'll try and track down some old code and see whether I did actually have the same error.

[DC Dalton]

Well now Im a bit stumped also. The error your getting (ORA-01009 - missing mandatory parameter) is without a doubt an Oracle error. My laptop is in the shop & it has my oracle & error messages stuff in it so I cant look up the exact error description for you. I looked thru Oracles site real quick but finding anything on that site is almost as bad as Microsoft's site. I dont think your having a driver problem (but I could be wrong) because your getting an actual Oracle error message back which means you are indeed connecting to the db, or at least to Oracle itself (if this is the case you may have the wrong driver). The query is now perfect if indeed your field name is cat & the table name is products. One thing about connecting to Oracle thru java I learned the hard way...do NOT include the ; inside your query string like you do in pl/sql. It doesnt look like your doing that but I just thought Id mention it. Do you have any constraints on the cat field? Also what version of Oracle are you running? Ill call a friend who has Oracle on & see if I can get the exact error message for that code later today.

[DC Dalton]

Wel that was quicker than I thought Id get it. Here is the exact Oracle error message from their API:
ORA-01009 missing mandatory parameter
Cause: A host program call did not pass all required
parameters
Action: Check the syntax for the call and enter all required
parameters
Unfortunately Oracles error API sometimes leaves a lot to be desired & I think this is one of those times. Did you set up the username & password when you made the connection? Thats the only thing I can think of at this point. If you using 8ai or 8i the username is scott, password is tiger:
Here is an example connection string I used on my local machine to connect to Oracle 8i:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String jdbc_url = "jdbc:oracle:thin@localhost:1521:oracle";
Connection conn = DriverManager.getConnection(jdbc_url, "scott", "tiger");
Thats the only help I can give at this point......wish I could do more. Unfortunately Im better at debugging in person that I am over the phone or emailing..

[This message has been edited by DC Dalton (edited July 03, 2001).]

[shawn sandy]

Thanks for the help, that will certainly give me a few options to work on. By the way, I am using Oracle 1.2 driver on 8i. Thanks again.