Home » SQL & PL/SQL » SQL & PL/SQL » How to ignore ORA-01927 and proceed with loop process (Oracle 10g)
How to ignore ORA-01927 and proceed with loop process [message #307848] Thu, 20 March 2008 02:54 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I have a loop process which on each iteration revokes SELECT privilege on a table(different table on each iteration). If an exception occurs on a particular iteration (ORA-01927) saying "cannot REVOKE privileges you did not grant", I need to ignore this and proceed with my next iteration, how to acheieve this ?
I have declared
PRAGMA EXCEPTION_INIT(revoke_ungranted_priv, -01927);
to handle this exception in exception section.
But how to handle this in exception section, so that the control would go back to the next iteration of the loop ?

Re: How to ignore ORA-01927 and proceed with loop process [message #307851 is a reply to message #307848] Thu, 20 March 2008 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start a PL/SQL block inside the loop: loop begin ... exception when ... end; end loop;

Regards
Michel
Re: How to ignore ORA-01927 and proceed with loop process [message #307852 is a reply to message #307848] Thu, 20 March 2008 03:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Add a block within the loop:
loop ....
  begin
    <revoke privilege>
  exception
    <handle the ORA-1927 exception>
  end;
end loop;
No guarantees. Still early for me and the coffee just doesn't seem to wake me up today. Very Happy

MHE
Re: How to ignore ORA-01927 and proceed with loop process [message #307854 is a reply to message #307851] Thu, 20 March 2008 03:13 Go to previous message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for the suggestions.
Previous Topic: How to store PDF file in CLOB datatype
Next Topic: Please, need an explanation of select results with multiple left joins
Goto Forum:
  


Current Time: Wed Dec 07 16:27:09 CST 2016

Total time taken to generate the page: 0.08629 seconds