Oracle11g 的 error "ORA-01747: user.table.column, table.column 或列说明无效


--查询数据库关键字

select * from v$reserved_words;

----查询表中是否有关键字

 select *
from v$reserved_words 
where keyword
in(
select COLUMN_NAME
from all_tab_columns
where table_name = '表名大写' and owner='用户名大写'
);

-------------------------

ORA_01747:User.table.column或列说明无效
此问题的原因是因为表的列名称使用了Oracle声明的关键字,解决方案:
1、首先我们要确认哪些字符串是Oracle的关键字,具体可通过Oracle提供的V$RESERVED_WORDS
2、对历史遗留系统的处理
考虑修改表的列名,风险较大
修改特定查询语句
3、验证
通过验证得出结论:
在查询列中使用双引号
要注意列的大小写
4、示例
 
CREATE TABLE WYC_TEST(CODE VARCHAR2(20), ADDR VARCHAR2(40));
INSERT INTO WYC_TEST(CODE,ADDR) VALUES('00', 'ADDR00');
ALTER TABLE WYC_TEST ADD "ROW" VARCHAR2(20);
ALTER TABLE WYC_TEST ADD "Row" VARCHAR2(20);
ALTER TABLE WYC_TEST ADD "RoW" VARCHAR2(30);
INSERT INTO WYC_TEST(CODE,ADDR, "ROW") VALUES('00', 'ADDR00', 'abc');
UPDATE WYC_TEST SET "ROW" = WYC_TEST.ADDR || '_ROW', "Row"=WYC_TEST.ADDR || '_Row', "RoW"=WYC_TEST.ADDR || '_RoW'
SELECT * FROM WYC_TEST;

阅读更多

java.sql.SQLException: ORA-01747: user.table.column, table.column 或列说明无效

12-26

错误代码rnjava.sql.SQLException: ORA-01747: user.table.column, table.column 或列说明无效rnrn at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.javarn:111)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)rn at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)rn at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.rnjava:212)rn at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStarntement.java:951)rn at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatemernnt.java:1159)rn at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreprnaredStatement.java:3284)rn at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparrnedStatement.java:3367)rn at com.baidu.UserBeanCl.updateUser(UserBeanCl.java:36)rn at com.baidu.UpdateCl.doGet(UpdateCl.java:25)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplrnicationFilterChain.java:290)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFrnilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVrnalve.java:233)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextVrnalve.java:191)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.jrnava:128)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.jrnava:102)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValrnve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.javrna:293)rn at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessornr.java:859)rn at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.prrnocess(Http11AprProtocol.java:574)rn at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:15rn27)rn at java.lang.Thread.run(Unknown Source)rn2009-12-26 16:02:57 org.apache.catalina.core.StandardContext reloadrn信息: Reloading this Context has startedrndestroy被调用rninit被调用rnsql=======update users set passwd='chensi',set email='chensi@qq.com',set grade='rn1' where userid='2'rnjava.sql.SQLException: ORA-01747: user.table.column, table.column 或列说明无效rnrn at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.javarn:111)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)rn at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)rn at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.rnjava:212)rn at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStarntement.java:951)rn at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatemernnt.java:1159)rn at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreprnaredStatement.java:3284)rn at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparrnedStatement.java:3367)rn at com.baidu.UserBeanCl.updateUser(UserBeanCl.java:36)rn at com.baidu.UpdateCl.doGet(UpdateCl.java:25)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplrnicationFilterChain.java:290)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFrnilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVrnalve.java:233)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextVrnalve.java:191)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.jrnava:128)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.jrnava:102)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValrnve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.javrna:293)rn at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessornr.java:859)rn at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.prrnocess(Http11AprProtocol.java:574)rn at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:15rn27)rn at java.lang.Thread.run(Unknown Source)

没有更多推荐了,返回首页