ORA-01722: invalid number

最新推荐文章于 2022-08-03 10:15:22 发布

cuixiuyin9014

最新推荐文章于 2022-08-03 10:15:22 发布

阅读量87

点赞数

今天在做查询时，遇到如下错误，简单记录一下
SQL> set linesize 200
SQL> col DEGREE for a10
SQL> SELECT OWNER,LEAF_BLOCKS,blevel,status,pct_free,index_name,degree from dba_indexes where degree !=1;
SELECT OWNER,LEAF_BLOCKS,blevel,status,pct_free,index_name,degree from dba_indexes where degree !=1
*
ERROR at line 1:
ORA-01722: invalid number

Cause: The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal. Only numeric fields
or character fields containing numeric data may be used in arithmetic functions
or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that
they contain only numbers, a sign, a decimal point, and the character "E" or "e"
and retry the operation.

SQL> desc dba_indexes
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)

从dba_indexes可以看到degree字段的类型为VARCHAR2，sql中输入的是数字，在进行字符转换时报错。

修改sql后查询正常
SQL> set linesize 200
SQL> col DEGREE for a10
SQL> SELECT OWNER,LEAF_BLOCKS,blevel,status,pct_free,index_name,degree from dba_indexes where degree !='1';

OWNER LEAF_BLOCKS BLEVEL STATUS PCT_FREE INDEX_NAME DEGREE
------------------------------ ----------- ---------- -------- ---------- ------------------------------ ----------
SYSTEM VALID 10 SYS_IL0000005928C00008$$ 0
SYSTEM    VALID 10 SYS_IL0000005637C00021$$ 0
SYS    VALID 10 SYS_IL0000005315C00048$$ 0
SYS    VALID 10 SYS_IL0000005283C00012$$ 0
SYS    VALID 10 SYS_IL0000005271C00006$$ 0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25373498/viewspace-1188348/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25373498/viewspace-1188348/