In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
ORACLE database, create a data table with char field columns: create table tbl_c (id char (1))
Insert the characters'1' and the number 2 on the table and execute the query:
Select * from tbl_c;SQL statement can be executed normally
Select * from tbl_c where id=1;SQL statement can be executed normally
Insert the character'A' into the table
Select * from tbl_c;SQL statement can be executed normally
Select * from tbl_c where id=1;SQL sentence query Times error ORA-01722: invalid number
After deleting the inserted character data'A', the query returns to normal.
The experiments are as follows:
1. Create an experimental table
SQL > create table tbl_c (id char (1))
Table created.
2. Insert normal data of the experiment
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. Test query
SQL > select * from tbl_c
I
-
one
two
SQL > select * from tbl_c where id=1
I
-
one
SQL >
4. Insert data that affect the parsing execution of CBO conditional queries.
SQL > insert into tbl_c values ('A')
1 row created.
SQL > commit
Commit complete.
SQL >
5. Test query, where conditional query, digital query has exception
SQL > select * from tbl_c where id='A'
I
-
A
SQL > select * from tbl_c where id='2'
I
-
two
SQL >
SQL > select * from tbl_c where id=1
ERROR:
ORA-01722: invalid number
No rows selected
With regard to this situation, the ORACLE MOS document Doc ID 1059215.1 explains:
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:
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.