Related to Oracle SQL production problems caused by implicit conversions when comparing the number type with the varchar2 type

After writing so much, I want to say that implicit conversion must be used with caution. This is like a time bomb ..

 

When I got off work the day before yesterday, I suddenly received a call saying that the online reimbursement system had a problem with the reservation and reimbursement function, which was urgently needed. Think about it .. It seems that there are no function updates recently. How can this problem suddenly become difficult after such a long time? I thought it should not be a big problem and I will be able to solve it in a moment. However, when I enter the development environment, I am stunned to see the error code .. SQL error, or invalid number .. how is it possible. An error occurs when a stored procedure is running. An error is returned when a cursor is fetch. I opened the database in the test environment (the configuration is the same as that in the formal environment), opened PLSQL, stuck the fetch cursor statement, ran the program, and added the breakpoint, obtain all the real-time parameters required for the stored procedure during running. Run the command in the variable corresponding to the where condition after the cursor statement. Yes .. I sweated at the time. Why ?? I threw the parameter directly into the test window of the stored procedure and ran it without an error. I had to connect to the official database and execute the same statement, which surprised me... Also passed. Test the stored procedure again, and the stored procedure reports an error at that fetch. Strange .. There is no problem when you execute the cursor statement separately. Why does fetch report an error .. I searched the internet and found that implicit type conversion may cause this problem. However, I think it is different from my current situation, but it gives me some inspiration. I read the input parameters and stored procedure variable types. It is found that implicit conversion exists in the cursor statement. The statement is as follows:

Select count (1)
From bxxxb

Where yyck = v_ckh (v_ckh is a variable in the stored procedure, in the number type, while the yyck field is in the dense type, and the window number is stored)

And to_char (RQ, 'yyyy-MM-DD ') = '2017-05-12'
And AMPM = 'am ';

 

After reading the following statement, we can see that it must be the error of yyck = v_ckh. When comparing values of numeric type and numeric type, Oracle will implicitly convert the numeric type to numeric type. Assume that the data type of the ID column is varchar2:
Select * from t where id = 1; equivalent to select * from t where to_number (ID) = 1

 

In this case, it must be because of a problem with the implicit to_number conversion. The to_number error must indicate that the field contains non-numeric data. It can also explain why the field can run normally in the test environment, but it does not work. However, in the formal environment, yyck stores the window number, which must be a number and should not contain a non-numeric window number. I selected it with a doubt, select yyck from bxxxb where yyck is not null order by yyck; I am puzzled by the result. When the condition is not null is used, a piece of data in the query result is empty, which is not a space, that is, the stored procedure throws an exception.

 

I thought that the brother who wrote this stored procedure did not expect this data to appear in this field .. Finally, we changed where yyck = v_ckh to where yyck = to_char (v_ckh), and everything went normal.

After writing so much, I want to say that implicit conversion must be used with caution. This is like a time bomb ..