Oracle Duplicate Index Error
Contents |
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java dup val on index exception Clipart Techie Humor Advertisement Oracle Basics Oracle Advanced Oracle Cursors Oracle dup_val_on_index in oracle 11g Exception Handling Named Programmer-Defined Exception Named System Exception WHEN OTHERS Clause SQLCODE SQLERRM Oracle Foreign Keys Oracle oracle predefined exceptions Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions NEXT: Named Programmer-Defined Exception Oracle / PLSQL: Named System Exceptions This
How To Handle Unique Constraint Exception In Oracle
Oracle tutorial explains how to use Named System Exceptions in Oracle/PLSQL with syntax and examples. What is a named system exception in Oracle? Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer. Oracle has a oracle dup_val_on_index continue standard set of exceptions already named as follows: Oracle Exception Name Oracle Error Explanation DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back. INVALID_CURSOR ORA-01001 You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in. LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username/password combination. NO_DATA_FOUND ORA-01403 You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package. TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and m
» Constraints: How to resolve the duplicated primary key exceptions By sysdba-Oracle on Aug 27, 2009 After disabling the constraints, One can oracle insert exception handling enter invalid values into the tables as per the constraint condition. for
Too_many_rows
example, if you disable primary key constraint, it is possible to enter the duplicate value in primary
Zero_divide
key column and commit the same data in table. till this you will not have the problem. Problem will arise if you try to enable the primary key. In https://www.techonthenet.com/oracle/exceptions/named_system.php presence of duplicate values. If your table is small it easy to judge what rows have invalid values but this problem will become a junk when your table is big, say millions of row. ........... but not to worry oracle database has a process to identify those row which have some invalid data as per the constraints. while https://blogs.oracle.com/sysdba/entry/constraints_how_to_resolve_the enabling the constraint, we can detect all rows which may have some invalid data. Following is one demo on small sample table:Let us say, we have a table CON with primary key in ID column.SQL> conn hr/hr Connected. SQL> desc con Name Null? Type ----------------------------------------- -------- ----------------------- ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(20) CITY VARCHAR2(10) SALARY NUMBER(10,2)SQL> select * from con; ID NAME CITY SALARY ---------- -------------------- ---------- ---------- 10 ABC B 1000 11 XYZ C 2000 13 DIFF 100 FFF 2000 12 SOME 2000 Let us disable the primary key, assuming constraint name of primary key in ID column is CON_PR. SQL> alter table con disable constraint con_pr;Table altered.SQL> select * from con; ID NAME CITY SALARY ---------- -------------------- ---------- ---------- 10 ABC B 1000 11 XYZ C 2000 12 FFF 2000 13 DIFFSQL> insert into con values(12,'SOME',NULL,2000);1 row created.SQL> commit;Commit complete.SQL> select * from con; ID NAME CITY SALARY ---------- -------------------- ---------- ---------- 10 ABC B 1000 11 XYZ C 2000 12 FFF 2000 13 DIFF 12 SOME 200
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeDUP_VAL_ON_INDEX Exception : Exception«PL http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/DUPVALONINDEXException.htm SQL Programming«Oracle PL/SQL TutorialOracle PL/SQL TutorialPL SQL ProgrammingExceptionThe DUP_VAL_ON_INDEX exception is raised when an attempt is made to store duplicate values in a column that is constrained by a unique index.SQL> -- create demo table SQL> create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary in oracle key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, oracle duplicate index Description) 2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) 2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester') 3 / 1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date