ORA-00972: identifier is too long报错的另外一种原因
来源:互联网 发布:周星驰演技 知乎 编辑:程序博客网 时间:2024/04/29 17:46
ORA-00972: identifier is too long这个错误最普通的原因就是字段太长了。
但是还有一种其他的情况:
不知道你是否遇到过这样的情况,一个sql语句,在plsqldeveloper中可以执行,但是在sqlplus中执行就报错ORA-00972: identifier is too long
这是因为字符集的问题。
看下面的例子:
SQL> SELECT
2 a.COLUMN_NAME 字段名,
3 DATA_TYPE||
4 decode(DATA_TYPE,'DATE','','CLOB','','BLOB','','BFILE','','FLOAT','','LONGRAW','','LONG','','RAW','(' || TO_CHAR(DATA_LENGTH) || ')',
5 (decode(SIGN(instr(DATA_TYPE,'CHAR')),1,'('|| TO_CHAR(DATA_LENGTH)||')',
6 (decode(SUBSTR(DATA_TYPE,1,9),'TIMESTAMP','',
7 (decode(NVL(DATA_PRECISION, -1), -1,'',
8 (decode(nvl(DATA_SCALE, 0), 0,'('||
9 to_char(DATA_PRECISION)||')','('||
10 to_char(DATA_PRECISION)||','||
11 to_char(DATA_SCALE) || ')'))))))))) as 字段类型,
12 b.comments 注释
13 FROM ALL_TAB_COLUMNS a,
14 all_col_comments b
15 WHERE b.table_name=a.TABLE_NAME
16 and a.OWNER=b.owner
17 and a.COLUMN_NAME=b.COLUMN_NAME
18 and a.TABLE_NAME =upper('gw')
19 AND a.OWNER =upper('sys')
20 ORDER BY COLUMN_ID;
b.comments 注重
*
ERROR at line 12:
ORA-00972: identifier is too long
这个语句在plsqldeveloper中就可以执行,不报错。
如果设置环境变量:
[oracle@gw1 dbs]$ export NLS_LANG='american_america.AL32UTF8'
再次执行查询,就不报错了:
SQL> SELECT
2 a.COLUMN_NAME 字段名,
3 DATA_TYPE||
4 decode(DATA_TYPE,'DATE','','CLOB','','BLOB','','BFILE','','FLOAT','','LONGRAW','','LONG','','RAW','(' || TO_CHAR(DATA_LENGTH) || ')',
5 (decode(SIGN(instr(DATA_TYPE,'CHAR')),1,'('|| TO_CHAR(DATA_LENGTH)||')',
6 (decode(SUBSTR(DATA_TYPE,1,9),'TIMESTAMP','',
7 (decode(NVL(DATA_PRECISION, -1), -1,'',
8 (decode(nvl(DATA_SCALE, 0), 0,'('||
9 to_char(DATA_PRECISION)||')','('||
10 to_char(DATA_PRECISION)||','||
11 to_char(DATA_SCALE) || ')'))))))))) as 字段类型,
12 b.comments 注释
13 FROM ALL_TAB_COLUMNS a,
14 all_col_comments b
15 WHERE b.table_name=a.TABLE_NAME
16 and a.OWNER=b.owner
17 and a.COLUMN_NAME=b.COLUMN_NAME
18 and a.TABLE_NAME =upper('gw')
19 AND a.OWNER =upper('sys')
20 ORDER BY COLUMN_ID;
字段名
------------------------------
字段类型
--------------------------------------------------------------------------------
注释
--------------------------------------------------------------------------------
NAME
CLOB
还有一种解决方法,就是将汉字改成英文。
[oracle@gw1 dbs]$ export NLS_LANG=
SELECT
a.COLUMN_NAME 字段名,
DATA_TYPE||
decode(DATA_TYPE,'DATE','','CLOB','','BLOB','','BFILE','','FLOAT','','LONGRAW','','LONG','','RAW','(' || TO_CHAR(DATA_LENGTH) || ')', ')',
(decode(SIGN(instr(DATA_TYPE,'CHAR')),1,'('|| TO_CHAR(DATA_LENGTH)||')',
(decode(SUBSTR(DATA_TYPE,1,9),'TIMESTAMP','',
(decode(NVL(DATA_PRECISION, -1), -1,'',
(decode(nvl(DATA_SCALE, 0), 0,'('||
to_char(DATA_PRECISION)||')','('||
to_char(DATA_PRECISION)||','||
to_char(DATA_SCALE) || ')'))))))))) as aaaaaaaaaaaaaaaaaa,
b.comments 注释
FROM ALL_TAB_COLUMNS a,
all_col_comments b
WHERE b.table_name=a.TABLE_NAME
16 and a.OWNER=b.owner
17 and a.COLUMN_NAME=b.COLUMN_NAME
18 and a.TABLE_NAME =upper('gw')
19 AND a.OWNER =upper('sys')
20 ORDER BY COLUMN_ID;
?????????
------------------------------
AAAAAAAAAAAAAAAAAA
--------------------------------------------------------------------------------
?????
--------------------------------------------------------------------------------
NAME
CLOB
如需转载,请注明出处:http://blog.csdn.net/nanaranran/article/details/22038291
- ORA-00972: identifier is too long报错的另外一种原因
- ORA-00972: identifier is too long
- An simple issue:ORA-00972: identifier is too long
- pl/sql报错:Identifier is too long
- pl/sql报错:Identifier is too long
- Hibernate(oracle)下的ORA:A-00972 identifier is too long提示
- 超過 Oracle 的長度限制 ORA-00972: identifier is too long
- oracle错误(三) ORA-00972 identifier is too long 的解决办法
- ora-12169:Net service name given as connect identifier is too long
- 关于"The parameter list is too long"错误的原因
- 在Linux执行命令报错”Arg list too long”的原因分析
- System Error Code 111 The file name is too long的一种可能的解决方式
- Data truncation: Data too long for column 'contents_o' at row 1 报错原因
- exp导出报错“ORA-00904: : invalid identifier ”的处理
- 0403-027 The parameter list is too long 参数过长报错
- AIX crontab 0403-027 The parameter list is too long 参数过长报错
- mysql报错:Specified key was too long; max key length is 767 bytes
- rm报错0403-027:The parameter list is too long
- 练就快速读书的功夫
- Enter
- (翻译)Java SE 8 Lambda 标准库概览(上)
- java简介
- Quake 攻略
- ORA-00972: identifier is too long报错的另外一种原因
- HDU 4597 Play Game(四维记忆化搜索)
- 为UIView及其子类添加圆角边框
- windows中LNK文件打开方式恢复(图形文件打开方式怎么都成CAD打开方式)
- apache 常用配置
- cocos2d-x android交叉环境配置 注意点
- http协议分析网页
- cocos2dx场景切换中init、onEnter、onEnterTransitionDidFinish的调用顺序
- android 断点续传