All geek questions in one place

Incorrect upgrade equivalent for Oracle

I need to run an Oracle query that will violate unique constraints, only on records that will not violate constraints. In MySQL, I believe that this can be done using the ignore command. Is there an equivalent in Oracle?

+6
source share
2 answers

You can use the Oracle error logging function to do this:

First you need to create a table that will later contain ignored rows:

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('YOUR_TABLE', 'YOUR_TABLE_ERROR_LOG');

This creates a table named YOUR_TABLE_ERROR_LOG for a table named YOUR_TABLE (obviously, you only need to do this once).

When you run your UPDATE, you need to add the LOG ERRORS :

 UPDATE your_table SET ... WHERE ... LOG ERRORS INTO YOUR_TABLE_ERROR_LOG ('UPDATE running at '||to_char(sysdate, 'yyyy-MM-dd HH24:MI:SS')) REJECT LIMIT UNLIMITED;

The specified string is an arbitrary value that helps identify the action that caused the error.

After the update, you can query the YOUR_TABLE_ERROR_LOG table to find out what errors occurred and why. If you are not interested in errors, just trim the error log table after that.

See the manual for more details:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10008.htm#BCEFBFCD

Change 2014-10-27

Since Oracle 11.2, there is a new tooltip called CHANGE_DUPKEY_ERROR_INDEX that can be used for this purpose. I have never tried this though.

Details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#CHDIFFJE

For INSERT operations, there is a similar prompt named IGNORE_ROW_ON_DUPKEY_INDEX :
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#CHDEGDDG

Some examples:

+8
source

It’s hard to say “no” completely (Oracle is big), but in 15 years of database programming I have never seen such a function in Oracle. You can disable restrictions, but this is not the same as what you are trying to accomplish here.

One way to solve the problem would be to write PL / SQL, which processes the table that you are updating row by row, trying to update, and swallow any errors. It will not be effective, but it will work. But in all the scenarios that I can imagine (if you don't have a very, very complex table), you should be able to write your update request to include the appropriate subqueries that work with restrictions. The exact strategy will depend on the tables and query.

0
source

Source: https://habr.com/ru/post/897434/

More articles:


All Articles