代码之家  ›  专栏  ›  技术社区  ›  Rachel

Oracle SQL错误:如何调试此问题

ora-00936 ora-00904 oracle sql
  •  0
  • Rachel  · 技术社区  · 14 年前

    使用: 

      select name, id 
    from "TEST" 
   where id :2 
     AND name :1 
order by id desc

    我得到 ORA: 00904 "TEST"."NAME": invalid identifier 错误,但wierd部分是我检查了我的测试表,它没有名称字段,但它有名称字段,我也检查了从该表和所有其他约束所做的所有引用,但它仍然给我同样的错误。我不知道为什么,有没有一种方法可以让我检查数据库的所有列中的名称列名称,或者任何其他推荐的debuggind方法都会受到高度欢迎。

    DESC试验; 名称空类型

    ID非空数字(11)
    姓名varchar2(29)

    选择2行

    编辑 

    DESC TEST
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
ID                             NOT NULL NUMBER(11)                                                                                                                                                                                    
NAME                                    VARCHAR2(29)                                                                                                                                                                                  

2 rows selected

    Java代码 

     public String searchExecute(HttpServletRequest req, javax.servlet.ServletContext ctx, String nextPage){
      // ESCA-JAVA0266:
      System.out.println("This is Awesome");
      // ESCA-JAVA0266:
      System.out.println("id:"+req.getParameter("s_1985"));
      // ESCA-JAVA0266:
      System.out.println("name:"+req.getParameter("s_1984"));

      boolean bDisplayAll = StringUtils.stringToBoolean(req.getParameter("display_all"));
        if(bDisplayAll)
            // ESCA-JAVA0034:
            req.setAttribute("c_display_all", "Y");
            req.setAttribute("c_search_submitted", "Y");
        return nextPage;
      }

    JSP代码 

    <%@ page import="att.leadx.dbutils.AppUtils" %>
<%@ page import="java.util.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-
1">
</head>
<link rel=stylesheet type=text/css href="leadx.css">
<body bgcolor="#FFFFFF"><BR>

<form NAME="forma" ACTION="dispatcher" METHOD="post">
    <INPUT TYPE="hidden" name="action" value="">
    <input type=hidden name="c_master" value="TEST_SEARCH">
    <%
        request.setAttribute("c_form", "TEST_SEARCH");
        request.setAttribute("c_top_title","PROFILE > TEST_SEARCH");
        request.setAttribute("c_top_link_image","images/setup_sm.gif");
        request.setAttribute("c_top_link_action","user.search.setup");
        request.setAttribute("c_top_link_app_func","search_setup");
        request.setAttribute("c_top_link_alt_display","Setup search criteria");
    %>
    <jsp:include page="j_custom_search.jsp" flush="true" />
        <table width="94%" border="0" cellspacing="1" cellpadding="3" align=center>
            <tr>
                <td colspan=4 align=center>
                <INPUT TYPE="submit" value="Search" class=prismsbutton onclick="document.forma.action.value ='cep.project.search.execute'">
                </td>
                <td colspan=4 align=center>
                <INPUT TYPE="button" value="Cancel" class=prismsbutton onclick="window.history.back()">
                </td>
            </tr>
        </table>
</form>
<form NAME="formd" ACTION="dispatcher" METHOD="post">
    <input type="hidden" name="c_jsp" value="j_test_dynamic_search.jsp">
    <%
        request.setAttribute("c_master", "TEST_SEARCH");
        request.setAttribute("c_html_form","formd");
        request.setAttribute("c_list_id","1984");
        request.setAttribute("c_search_form","TEST_SEARCH");
        Object args[] = att.utils.DataStore.arg(att.leadx.dbutils.AppUtils.getLoggedInUser(request), att.utils.DataStore.TYPE_NUMERIC);
        request.setAttribute("c_args", args);
        if (att.utils.StringUtils.stringToBoolean((String)request.getAttribute("c_search_submitted"))){
            %>
                <jsp:include page="j_master_detail_dlist.jsp" flush="true" />
            <%
        }
    %>
</form>

    <%!
        private void o(String s){
            if (att.leadx.dbutils.AppUtils.inDebug())
            System.out.println("[J_USER_SEARCH.JSP] " + s);
        }
    %>

    SQL语句

    select name, id from TEST where name = :1 AND id = :2 order by id desc

    错误信息 

    Aug 16, 2010 5:22:21 PM org.apache.catalina.core.ApplicationDispatcher invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: ORA-00936: missing expression

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2110)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:627)
    at att.utils.DataStore.retrieve(DataStore.java:724)
    at org.apache.jsp.j_005fmaster_005fdetail_005fdlist_jsp._jspService(j_005fmaster_005fdetail_005fdlist_jsp.java:566)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:369)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:308)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:259)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:659)
    at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:565)
    at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:493)
    at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:965)
    at org.apache.jsp.j_005ftest_005fdynamic_005fsearch_jsp._jspService(j_005ftest_005fdynamic_005fsearch_jsp.java:102)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:369)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:308)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:259)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:659)
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:457)
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:395)
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:311)
    at att.leadx.nav.Dispatcher.service(Dispatcher.java:113)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
    at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
    at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
    at java.lang.Thread.run(Unknown Source)

    谢谢

    2 回复  |  直到 13 年前
        1
  •  4
  •   cagcowboy    14 年前 
    SELECT *
FROM   ALL_TAB_COLS
WHERE  UPPER(COLUMN_NAME) = 'NAME';

    将显示所有名为name的列

    编辑:

    根据您的评论,您是否在WHERE子句中缺少运算符?I= 

    select name, id
from   "TEST"
where  id :2      -- Surely you mean: id = :2
AND name :1       -- Surely you mean: name = :2
order by id desc

    编辑2:

    基于SQL*PLUS输出,您似乎创建了具有小写列名的表。虽然这是可能和有效的,但通常只是努力工作。我将用大写字母重新创建列。(正如亚历克斯所说)

    编辑3:

    我想。。。 

    SELECT "id", "name"
FROM   TEST
WHERE  "id" = :1
AND    "name" = :2
ORDER BY "id" desc;

    应该工作

        2
  •  2
  •   Sergey Stadnik    14 年前

    最好不要用双引号括住列名和表名,除非您知道自己在做什么,并且确信自己需要这样做。

    在Oracle中,数据库对象名(包括表和列)不区分大小写,假定为大写。 除非 您将它们包括在双引号中。

    例如,如果表创建为 TEST ,可称为 test , Test 或 TesT 或 "TEST" . 但是如果它被创建为 "Test" ,您只能将其称为 “测试” (双引号)。所有其他方法都会导致“无效标识符”错误。

    推荐文章
    关于  
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号