ORA-00972: identifier is too long报错的另外一种原因

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