BUG caused by Instr

Yesterday afternoon, JAVA developers of the Technology Department reported that online databases could not delete data. They asked me to check whether there was any lock in the database. I thought it was a deadlock or congestion. I did not find any lock! Ask it to send the statement, and then send the table name! Find the delete statement SELECTSQL_ID, PIECE, SQL _TEXTF Based on the table name

Yesterday afternoon, JAVA developers of the Technology Department reported that online databases could not delete data. They asked me to check whether there was any lock in the database. I thought it was a deadlock or congestion. I did not find any lock! Ask it to send the statement, and then send the table name! SELECT SQL _ID, PIECE, SQL _TEXTF

Yesterday afternoon, the JAVA developer of the Technology Department reported that online databases could not delete data. I want to check whether the database is locked. I thought it was a deadlock or congestion.

No such information is found!

Ask it to send the statement, and then send the table name!

Find the delete statement based on the table name

SELECT SQL_ID,PIECE,SQL_TEXTFROM V$SQLTEXT_WITH_NEWLINESWHERE SQL_ID IN(SELECT SQL_IDFROM V$SQLTEXT_WITH_NEWLINESWHERE SQL_TEXT LIKE '%table_name%')ORDER BY SQL_ID,PIECE;

Three statements starting with the DELETE statement are found to identify the variables by the statement ID.

select * from v$sql_bind_capture where sql_id in ('fpkt91ujqu853');

Gquw3xgbqvu13: delete from paynum p where instr (: 1, ',' | PL_ID | ',')> 02014-3-12 17:44:13, 3249, b5yjgrqx6j2dm: delete from paynum where instr (: 1, ''| PL_ID |'')> 02014-3-13 17:45:02, 3319, fpkt91ujqu853: delete from paynum_ELEMENT e where instr (: 1, ',' | e. ple_pl_id | ',')> 02014-3-13 17:45:02, 3319, where pay_element is the associated table. The foreign key with paynum in the table is the error reported by this foreign key constraint.

From the above, there are two different SQL statements for deleting paynum. The first one is old, and the second one is recently released.

Based on the value of the variable, You can first Delete the input variables ', 3319,' in the joined table and then delete the PAYNUM table with the same input variables ', 3319 ,'

We can also see that parameters: 1 can not only enter a single variable value, but also a string of variable values such as ', 3251,3250, 3248, 3247 ,'

The paynum_ELEMENT table associated with the record query value of 3319 from the data table has been deleted, and the PAYNUM still exists!

In summary

delete from paynum paynum WHERE INSTR(',3319,',''||PL_ID||'') > 0

It is hard for me to understand and guess such a statement as an ordinary person on the earth. Through my own efforts, I finally understood it and wrote WANG Xing!

select pl_id, INSTR(',3319,',''||PL_ID||'') from paynum;

We can see that a virtual column is created first, and its values are compared between PL_ID and 3319. If the value is greater than 0, the column is deleted;

I tested it to delete records. However, the online behavior is shown as follows:

PL_IDINSTR('3319',''||PL_ID||'')1331233133113319122790228202281022800

INSTR

(Source string, target string, start position, matching serial number)

In Oracle/PLSQL, The instr function returns the position of the string to be truncated in the source string. Only retrieval once, that is, starting from the character

Did you find anything?

The opposite INSTR (', 3319,', ''| PL_ID | '')

The normal format is INSTR (''| PL_ID |'', ', 3319 ,')

The normal design is:

delete from paynum paynum where paynum.pl_id=to_number(:?)