Dup_val_on_index Error
Contents |
Code Library mySQL Code
How To Handle Unique Constraint Exception In Oracle
Library PHP Code Library JavaScript Code Library Oracle Terms & Definitions Oracle Error Codes PSOUG oracle predefined exceptions Community Blogs Oracle Jobs Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and Events Sponsors Page Submit Code others exception in oracle Contact Us Looking for the original pages? (formerly called "Morgan's Library") You can find them here. Term: DUP_VAL_ON_INDEX Definition: The DUP_VAL_ON_INDEX Exception (ORA-00001) occurs when a program attempts to store a duplicate value or values in a database column that is constrained by a unique index. Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 55 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this
Too_many_rows
site About Us Learn more about Stack Overflow the company Business Learn oracle insert exception handling more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Raise Application Error Oracle
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle http://psoug.org/definition/dup_val_on_index.htm INSERT INTO SELECT(…) DUP_VAL_ON_INDEX exception behavior up vote 0 down vote favorite I have a stored procedure which looks like this: BEGIN INSERT INTO result_table (SELECT (...) FROM query_table); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END; I'm doing it in a loop which passes multiple parameters to the SELECT statement and in some cases some of the values might duplicate that is why I have http://stackoverflow.com/questions/14649622/oracle-insert-into-select-dup-val-on-index-exception-behavior to catch the DUP_VAL_ON_INDEX exception. My question is that if the SELECT statement returns more rows and only one from them exists already in *result_table*, f. ex. 1 'A' 2 'B' 3 'C' And first row (1 'A') would already be in the table, would other rows which don't exist (second and third from case above) be inserted? Or none of them would be inserted at all? I'm afraid that none of them would be inserted (and my test case partially confirms that)... If so, what option do I have to achieve desired bahavior? Is there a good way to insert the rows that don't violate the primary key using the construction above? oracle exception share|improve this question asked Feb 1 '13 at 15:30 Ziouas 4341317 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote accepted You can use the MERGE statement. Insert the records if they don't exist and do nothing if they already exist. http://psoug.org/reference/merge.html share|improve this answer answered Feb 2 '13 at 9:54 Alen Oblak 2,307519 That is a really helpful answer and it looks like it shold work t
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 http://www.dbasupport.com/forums/showthread.php?40744-error-on-when-dup_val_on_index-then 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, exception in oracle 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, St
when dup_val_on_index then....... If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 3 of 3 Thread: error on when dup_val_on_index then....... Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 01-07-2004,09:50 AM #1 raf View Profile View Forum Posts Member Join Date Jul 2002 Posts 228 error on when dup_val_on_index then....... Hi, I'd like to change message: ORA-00001: unique constraint (SCOTT.PK_DEPT) violated if I create a procedure: begin INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'aaaa', 'bbbbb'); exception when dup_val_on_index then raise_application_error( -20100, 'change your value please!' ); end; it run correctly. But I'd like to create a trigger that fire when I insert a new record. I tried this: CREATE OR REPLACE TRIGGER dept_test after INSERT ON dept FOR EACH ROW BEGIN if inserting then null; end if; exception when dup_val_on_index then raise_application_error( -20100, 'cambia il tuo valore, esiste' ); END dept_TEST; but It doesn't run How can I create this trigger?? Thanks Raf Reply With Quote 01-07-2004,10:06 AM #2 slimdave View Profile View Forum Posts Visit Homepage Foreign Script Kiddie Join Date Aug 2002 Location Colorado Springs Posts 5,253 Re: error on when dup_val_on_index then....... Originally posted by raf But I'd like to create a trigger that fire when I insert a new record. I tried this: CREATE OR REPLACE TRIGGER dept_test after INSERT ON dept FOR EACH ROW BEGIN if inserting then null; end if; exception when dup_val_on_