Home » SQL & PL/SQL » SQL & PL/SQL » ERROR CODE IS 1 User-Defined Exception Instead of ORA-00001: unique constraint (SCOTT.PK_EMP_UP) vi (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
ERROR CODE IS 1 User-Defined Exception Instead of ORA-00001: unique constraint (SCOTT.PK_EMP_UP) vi [message #644164] Wed, 28 October 2015 23:09 Go to next message
rajesh_oracle
Messages: 2
Registered: October 2015
Junior Member
SET SERVEROUTPUT ON;
DECLARE
type t1
IS
TABLE OF emp.empno%type;
v_t1 T1;
ex_dml_errors EXCEPTION;
pragma exception_init(ex_dml_errors,-00001);
BEGIN
SELECT EMPNO BULK COLLECT INTO v_t1 FROM emp;
FORALL I IN v_t1.first..v_t1.last
UPDATE emp SET empno=v_t1(i);
EXCEPTION
WHEN ex_dml_errors THEN
FOR j IN 1..SQL%BULK_EXCEPTIONS.count
LOOP
DBMS_OUTPUT.PUT_LINE('ERROR CODE IS '||SQL%BULK_EXCEPTIONS(j).error_code||' '||sqlerrm(SQL%BULK_EXCEPTIONS(J).error_code));
END LOOP;
END;

-----

anonymous block completed
ERROR CODE IS 1 User-Defined Exception


Code has displayed below error message "ERROR CODE IS 1 User-Defined Exception" Instead of Below Error Message
"ORA-00001: unique constraint (SCOTT.PK_EMP_UP) violated
00001. 00000 - "unique constraint (%s.%s) violated"


Find The attached "Bulk Exception.jpg" file for the same.

Report message to a moderator

Re: ERROR CODE IS 1 User-Defined Exception Instead of ORA-00001: unique constraint (SCOTT.PK_EMP_UP) vi [message #644165 is a reply to message #644164] Wed, 28 October 2015 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
never do in PL/SQL that which can be done in plain SQL

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

ORA-00001 means a UNIQUE constraint exists & it was violated.

Report message to a moderator

Re: ERROR CODE IS 1 User-Defined Exception Instead of ORA-00001: unique constraint (SCOTT.PK_EMP_UP) vi [message #644168 is a reply to message #644164] Thu, 29 October 2015 00:25 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
As the UPDATE statement inside FORALL statement lacks the WHERE clause, it updates all rows of EMP table in each iteration. So in the end, all rows in the EMP table would have the value of the row which was fetched last.
Which is restricted when EMP table has more than one row.

All the code makes no sense to me, so I cannot recommend any "fix". Why would you UPDATE the column with the same value that you are fetching?
If this is just for learning purposes, have a look at PL/SQL Language Reference for your Oracle version, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html - there are many examples of FORALL statement there.

Report message to a moderator

Re: ERROR CODE IS 1 User-Defined Exception Instead of ORA-00001: unique constraint (SCOTT.PK_EMP_UP) vi [message #644171 is a reply to message #644164] Thu, 29 October 2015 01:00 Go to previous message
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Also feedback and thank people who help you here and in your previous topic.

Report message to a moderator

Previous Topic: Insert, delete and update At a time
Next Topic: Sending SMS through Oracle Database
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jan 13 22:46:30 CST 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.