溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

ORACLE 表char字段混合存儲數字和字母類似數據時按數字的where條件查詢報錯ORA-01722

發布時間:2020-08-10 14:38:19 來源:ITPUB博客 閱讀:176 作者:清風艾艾 欄目:關系型數據庫

    ORACLE 數據庫,創建有char字段列的數據表:create table tbl_c (id char(1));

對該表插入字符'1'、數字2,執行查詢:

select * from  tbl_c;SQL語句能夠正常執行,

select * from  tbl_c where id=1;SQL語句能夠 正常 執行;

表中插入字符'A’,

select * from  tbl_c;SQL語句能夠 正常 執行

select * from  tbl_c where id=1;SQL語句查詢時報錯 ORA-01722: invalid number 

刪除插入的字符數據'A'后,查詢恢復正常;

    實驗如下:

1、創建實驗表

SQL> create table tbl_c (id char(1));

Table created.


2、插入實驗 正常 數據

SQL> insert into tbl_c values('1');

1 row created.

SQL> insert into tbl_c values(2);

1 row created.

SQL> commit;

Commit complete.


3、測試查詢

SQL> select * from tbl_c;

I

-

1

2

SQL>  select * from  tbl_c where id=1;

I

-

1

SQL> 


4、插入影響CBO條件查詢解析執行的數據

SQL> insert into tbl_c values('A');

1 row created.

SQL> commit;

Commit complete.

SQL> 


5、測試查詢,where條件查詢數字查詢出現異常

SQL> select * from  tbl_c where id='A';

I

-

A

SQL> select * from  tbl_c where id='2';

I

-

2

SQL> 

SQL> select * from  tbl_c where id=1;

ERROR:

ORA-01722: invalid number

no rows selected

    關于這種現狀,ORACLE MOS文檔 Doc ID 1059215.1 有相關說明:

CAUSE

The problem is due to the way Oracle handles datatype conversions.
The statement being executed is made to compare the VARCHAR database field with a numeric literal value.
In this case, the database does an implicit conversion applying the TO_NUMBER function to the first column, in order to be able to perform the comparison between comparable values.
If this column contains non-numeric values and one of them is retrieved and the condition is evaluated, the ORA-1722 error is issued.

SOLUTION

The proposed solution is to avoid the implicit conversion. This may be achieved in two ways:
A) Modify the data to assure no invalid numeric values are stored in the column, either updating invalid column values or modifying the column datatype to enforce the presence of only numeric values. This implies the application remains unchanged.
This solution implies a modification in the database model, or to add restrictions not necessarily applicable to the table column involved.

OR

B) Modify the application to ensure the comparison is done between two VARCHAR values. This may be easily achieved surrounding the variable value with quotes:

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

亚洲午夜精品一区二区_中文无码日韩欧免_久久香蕉精品视频_欧美主播一区二区三区美女