(on) Hints for Online Application Upgrade

ERROR at line 1:
ORA-00001: unique constraint (UADMIN.T1PK) violated

INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T1, T1PK) */ INTO T1 (N1, N2, N3, N4)
SELECT 0, 0, 0, 0 FROM DUAL UNION ALL
SELECT -1, -1, -1, -1 FROM DUAL;

ERROR at line 1:
ORA-38911: unique constraint (UADMIN.T1PK) violated

UPDATE T1 SET N1 = 0 WHERE N1 = 1;

ERROR at line 1:
ORA-00001: unique constraint (UADMIN.T1PK) violated

UPDATE /*+ CHANGE_DUPKEY_ERROR_INDEX(T1, T1PK) */ T1 SET N1 = 0 WHERE N1 = 1;

ERROR at line 1:
ORA-00001: unique constraint (UADMIN.T1PK) violated

– PL/SQL 에서 DUP_VAL_ON_INDEX 와 EXCEPTION 을 분리 하고자 할때 사용?

|

||IGNORE_ROW_ON_DUPKEY_INDEX||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/ignore_row_on_dupkey_index.gif!|borderStyle=solid}
INSERT INTO T1 (N1, N2, N3, N4) 
SELECT  0,  0,  0,  0 FROM DUAL UNION ALL
SELECT -1, -1, -1, -1 FROM DUAL;

ERROR at line 1:
ORA-00001: unique constraint (UADMIN.T1PK) violated


INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T1, T1PK) */ INTO T1 (N1, N2, N3, N4) 
SELECT  0,  0,  0,  0 FROM DUAL UNION ALL
SELECT -1, -1, -1, -1 FROM DUAL;

1 row created.

-- INSERT

|

– http://rwijk.blogspot.com/2009/10/three-new-hints.html
– https://sites.google.com/site/oraclepracticals/oracle-sql/retry_on_row_change-hint
– http://blog.naver.com/PostView.nhn?blogId=inhim&logNo=100118507293
– http://ukja.tistory.com/297

|