sql - jdbc.BadSqlGrammarException 表或 View 不存在。在数据库中测试查询，有效。但是网络应用程序错误

Question

EDIT: FIXED! Look below

所以我在 SQL Developer 中测试了该查询，它工作正常。但是，在我的实现中，运行不会将数据加载到列表中并吐出以下错误:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select columnname,displayname, viewsortorder,capsdisind,window, DIS FIELDID , (select 1 from WEB_CUSTDISABLEDFIELDS where custid =? AND A.DISFIELD ID = WEB_CUSTDISABLEDFIELDS.DISFIELDID) from web_DISABLEDFIELDS A ]; nested ex ception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.d oTranslate(SQLErrorCodeSQLExceptionTranslator.java:220) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 607) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:64 1) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:67 0) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:67 8) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:71 0) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdap ter.java:421) at flex.messaging.services.RemotingService.serviceMessage(RemotingServic e.java:183) at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java :1503) at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndp oint.java:884) at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBroker Filter.java:121) at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:15 8) at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java: 44) at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFi lter.java:67) at flex.messaging.endpoints.amf.SerializationFilter.invoke(Serialization Filter.java:146) at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.ja va:278) at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java :322) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl icationFilterChain.java:304) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF ilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV alve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV alve.java:175) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica torBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j ava:164) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j ava:100) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java: 929) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal ve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav a:405) at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcesso r.java:316) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process( AbstractProtocol.java:515) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoin t.java:1544) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.ja va:91) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:1 12) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :173) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement. java:194) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPrepare dStatement.java:785) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPrepa redStatement.java:860) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme nt.java:1186) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep aredStatement.java:3381) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePrepare dStatement.java:3425) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(Oracle PreparedStatementWrapper.java:1490) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(Jd bcTemplate.java:648) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 591) ... 39 more

我已经搜索了多个与此错误有关的问题，但似乎没有一个问题能解决与我的问题相同的问题。我正在使用 Oracle SQL。

也许有人以前遇到过这个问题？任何帮助表示赞赏。谢谢!

解决了问题

问题出在表格上。他们只需要访问数据库的权限即可。

我所做的是:

grant all on sa.table to public
/
create public synonym table for sa.table
/
grant all on sa.table2 to public
/
create public synonym table2 for sa.table2
/

Answer 1

您的网络应用程序很可能登录到与表不同的架构

尝试在 sql 语句中显式指定架构。即:

SELECT a, b FROM user.TABLE