| Filter on a specific error [message #187814] |
Tue, 15 August 2006 15:39  |
Bill_N
Messages: 3
Registered: August 2006
|
Junior Member |
|
|
I have the following anonymous pl/sql block shown below:
FOR v_Rec IN get_users LOOP
vUser := v_Rec.user_name;
BEGIN
execute immediate 'ALTER USER ' ||vUser||' IDENTIFIED BY BlahBlah';
EXCEPTION
WHEN OTHERS THEN RAISE;
END;
END LOOP;
Everything works okay except if a user already has the password BlahBlah in which I receive the error:
ORA-28007: the password cannot be reused
How can I trap this error and go to the next record rather than raising the error? How can I say something like when password is already BlahBlah ignore it?
FOR v_Rec IN get_users LOOP
vUser := v_Rec.user_name;
BEGIN
execute immediate 'ALTER USER ' ||vUser||' IDENTIFIED BY Contrax01';
EXCEPTION
WHEN ORA-28007: THEN IGNORE_this;
WHEN OTHERS THEN RAISE;
END;
END LOOP;
Any help would be much appreciated.
|
|
|
|
| Re: Filter on a specific error [message #187832 is a reply to message #187814] |
Tue, 15 August 2006 21:50  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| Barbara Boehmer wrote on Wed, 16 August 2006 09:00 |
...
WHEN ORA-28007 THEN NULL;
...
|
Does that work? I thought you had to use a PRAGMA EXCEPTION_INIT. I did a quick test with this syntax and got an error. Maybe its a 10g thing....?
The following should work if you cant get BB's going.
FOR v_Rec IN get_users LOOP
vUser := v_Rec.user_name;
DECLARE
ORA28007 EXCEPTION;
PRAGMA EXCEPTION_INIT (ORA28007, -28007);
BEGIN
execute immediate 'ALTER USER ' ||vUser||' IDENTIFIED BY Contrax01';
EXCEPTION
WHEN ORA28007 THEN NULL;
WHEN OTHERS THEN RAISE;
END;
END LOOP;
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Filter on a specific error [message #187929 is a reply to message #187849] |
Wed, 16 August 2006 06:56 |
Bill_N
Messages: 3
Registered: August 2006
|
Junior Member |
|
|
Thanks for all the responses! What a great forum.
However, the script didn't exactly do what I had hoped it would. I wanted to iterate through a cursor and change all the passwords to a common one in a test db. If it encounters a password that is the same as to what I'm setting it to, ignore error and go to next item in cursor.
DECLARE
vUser VARCHAR2(20);
ORA28007 EXCEPTION;
CURSOR get_users IS
SELECT a.user_name
FROM blah a,DBA_users b
WHERE a.user_name = b.username AND
ORDER BY a.user_name;
BEGIN
FOR v_Rec IN get_users LOOP
vUser := v_Rec.user_name;
BEGIN
execute immediate 'ALTER USER ' ||vUser||' IDENTIFIED BY Blah01';
DBMS_OUTPUT.put_line(vUser||' has been updated. ');
EXCEPTION
WHEN ORA28007 THEN null;
WHEN OTHERS THEN RAISE;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line(vUser||' : '||SQLERRM);
END;
Currently though, it gets to the first user who has Blah01 as her password and errors out returns this error message AJONES : ORA-28007: the password cannot be reused
Any ideas?
Thanks, Bill N
|
|
|
|
| Re: Filter on a specific error [message #187936 is a reply to message #187929] |
Wed, 16 August 2006 07:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You are missing a like in the DECLARE section, to associate the error ORA-28007 with your exception.
You need a line like
PRAGMA EXCEPTION_INIT (ORA28007, -28007);
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
