Home » SQL & PL/SQL » SQL & PL/SQL » Filter on a specific error
Filter on a specific error [message #187814] Tue, 15 August 2006 15:39 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #187839 is a reply to message #187832] Tue, 15 August 2006 23:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You're quite correct. I browsed over it too quickly and thought the problem was just how to "ignore_this" and copied and pasted and substituted "null" for "ignore_this", completely missing the rest of the problem.
Re: Filter on a specific error [message #187849 is a reply to message #187839] Wed, 16 August 2006 00:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the use of a
WHEN OTHERS THEN RAISE
clause??
The only purpose it serves is that it obfuscates the line number where the actual error occurred.
Re: Filter on a specific error [message #187929 is a reply to message #187849] Wed, 16 August 2006 06:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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);
as @rleishman had in his example.
icon6.gif  Re: Filter on a specific error [message #187939 is a reply to message #187936] Wed, 16 August 2006 07:09 Go to previous message
Bill_N
Messages: 3
Registered: August 2006
Junior Member
Oops forgot the
PRAGMA EXCEPTION_INIT (ORA28007, -28007);	
.

Thanks again.
Previous Topic: Date Format
Next Topic: how to delete the users
Goto Forum:
  


Current Time: Fri Dec 09 15:56:17 CST 2016

Total time taken to generate the page: 0.18615 seconds