最近在做MP项目的时候涉及到Oracle10gR2数据库相关的编程,需要一些测试数据,我就在SQL Developer中用SQL语句向表中添加记录:
INSERT INTO UserActionLog (log_source,user,action,terminal,time) VALUES (...);
运行该脚本显示出错:ORA-01747:user.table.column,table column或列说明无效。
但我在图形界面下手动向表格中添加内容就可以提交成功,控制台显示生成的SQL语句是:
INSERT INTO "SYSTEM"."UserActionLog" ("log_source","user","action","terminal","time") VALUES (...,TO_DATE('28-4月-11‘,'DD-MON-RR'));
对比SQL语句可以发现有两点不同:
1.所有的表名、列名都包含在双引号中
2.添加了SYSTEM表空间
于是我尝试只添加表空间SYSTEM,发现依然报错;只添加双引号,也是报错。看来只有同时应用才可以。
这让我想到了《Oracle Database 10g 完全参考手册》中创建数据表的一些建议(P280):
-“各列定义由逗号分隔,...表名和列名必须以字母表中的字母开始,可以包括字母,数字和下划线,长度为1~30个字符,...,不能用Oracle保留字”
-“如果名称没有用双引号括起来,则在创建表时不用考虑字母的大小写”
-“注意:不要用双引号把表名和列名括起来,否则将出问题,这会给用户和开发者造成灾难”
我百度了一下ORA-01747,发现普遍是由于保留字引起的,在SQL语句输入界面,user和time确实被高亮了,就像INSER INTO一样。
查看Oracle保留字的SQL语句:
SELECT * FROM v$reserved_words;
Oracle保留字有:
选定了 1,142 行
!
&
(
)
*
+
,
-
------------------------------
.
/
:
<
<<
=
>
@
A
ABORT
ACCESS
ACCESSED
ACCOUNT
ACTIVATE
ADD
ADMIN
ADMINISTER
ADMINISTRATOR
ADVISE
ADVISOR
AFTER
ALIAS
ALL
ALLOCATE
ALLOW
ALL_ROWS
ALTER
ALWAYS
ANALYZE
ANCILLARY
AND
AND_EQUAL
ANTIJOIN
ANY
APPEND
APPLY
ARCHIVE
ARCHIVELOG
ARRAY
AS
ASC
ASSOCIATE
AT
ATTRIBUTE
ATTRIBUTES
AUDIT
AUTHENTICATED
AUTHENTICATION
AUTHID
AUTHORIZATION
AUTO
AUTOALLOCATE
AUTOEXTEND
AUTOMATIC
AVAILABILITY
BACKUP
BATCH
BECOME
BEFORE
BEGIN
BEGIN_OUTLINE_DATA
BEHALF
BETWEEN
BFILE
BIGFILE
BINARY_DOUBLE
BINARY_DOUBLE_INFINITY
BINARY_DOUBLE_NAN
BINARY_FLOAT
BINARY_FLOAT_INFINITY
BINARY_FLOAT_NAN
BINDING
BITMAP
BITMAPS
BITMAP_TREE
BITS
BLOB
BLOCK
BLOCKS
BLOCKSIZE
BLOCK_RANGE
BODY
BOTH
BOUND
BROADCAST
BUFFER
BUFFER_CACHE
BUFFER_POOL
BUILD
BULK
BY
BYPASS_RECURSIVE_CHECK
BYPASS_UJVC
BYTE
CACHE
CACHE_CB
CACHE_INSTANCES
CACHE_TEMP_TABLE
CALL
CANCEL
CARDINALITY
CASCADE
CASE
CAST
CATEGORY
CERTIFICATE
CFILE
CHAINED
CHANGE
CHAR
CHARACTER
CHAR_CS
CHECK
CHECKPOINT
CHILD
CHOOSE
CHUNK
CIV_GB
CLASS
CLEAR
CLOB
CLONE
CLOSE
CLOSE_CACHED_OPEN_CURSORS
CLUSTER
CLUSTERING_FACTOR
COALESCE
COARSE
COLLECT
COLUMN
COLUMNS
COLUMN_STATS
COLUMN_VALUE
COMMENT
COMMIT
COMMITTED
COMPACT
COMPATIBILITY
COMPILE
COMPLETE
COMPOSITE_LIMIT
COMPRESS
COMPUTE
CONFORMING
CONNECT
CONNECT_BY_COST_BASED
CONNECT_BY_FILTERING
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
CONNECT_BY_ROOT
CONNECT_TIME
CONSIDER
CONSISTENT
CONSTRAINT
CONSTRAINTS
CONTAINER
CONTENT
CONTENTS
CONTEXT
CONTINUE
CONTROLFILE
CONVERT
CORRUPTION
COST
CPU_COSTING
CPU_PER_CALL
CPU_PER_SESSION
CREATE
CREATE_STORED_OUTLINES
CROSS
CUBE
CUBE_GB
CURRENT
CURRENT_DATE
CURRENT_SCHEMA
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR
CURSOR_SHARING_EXACT
CURSOR_SPECIFIC_SEGMENT
CYCLE
DANGLING
DATA
DATABASE
DATAFILE
DATAFILES
DATAOBJNO
DATE
DATE_MODE