Home » SQL & PL/SQL » SQL & PL/SQL » Exit from a sql script based on condition
Exit from a sql script based on condition [message #223562] Fri, 09 March 2007 09:40 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I want to exit from a sql script based on a condition like

if (v = 0) then
exit;
end if;

I am not able to use exit inside a pl/sql script , is there any other alternative through which I can get out of a script based on a condition.
Re: Exit from a sql script based on condition [message #223575 is a reply to message #223562] Fri, 09 March 2007 11:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One way I have used a couple of Times :

WHENEVER SQLERROR EXIT

SELECT 'Hi There' x FROM dual;

SELECT 'Still here' x FROM dual;

--break here
BEGIN
   IF 1 = 1 THEN
      raise_application_error (-20100, 'User Exit');
   END IF;
END;
/


SELECT 'Not anymore' x FROM dual;

EXIT;


You have a "WHENEVER SQLERROR EXIT" at the start of the script, then you raise a user defined exception at the point where you wish to exit.

If you don't want to exit on other SQL Errors, you would have to put the WHENEVER SQLERROR EXIT rigbt before the "begin", and a WHENEVER SQLERROR CONTINUE right after the / after the end.
Re: Exit from a sql script based on condition [message #223680 is a reply to message #223575] Sat, 10 March 2007 04:49 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Thanks Thomas
But what you have said will throw a error message to the user running the script right?

like
ERROR at line 1:
ORA-20100: User Exit
ORA-06512: at line 3

I dont want a error message to be displayed at the prompt , it should come out of the script without throwing any errors .

Is that possible??????
Re: Exit from a sql script based on condition [message #223689 is a reply to message #223680] Sat, 10 March 2007 07:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is it you want to call if v <> 0?
Can't you give v as parameter to that and let that procedure do nothing if v = 0?
Re: Exit from a sql script based on condition [message #223693 is a reply to message #223680] Sat, 10 March 2007 08:50 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
rolex.mp wrote on Sat, 10 March 2007 10:49
I dont want a error message to be displayed at the prompt , it should come out of the script without throwing any errors .


SET TERMOUT OFF

I still say scripts suck though Wink
Re: Exit from a sql script based on condition [message #223695 is a reply to message #223562] Sat, 10 March 2007 08:55 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
No , The script contains many anonymous blocks so I want to execute few anonymous blocks based on a condition set by the previous anonymous blocks in the same script

So I coded the anonynmous blocks that has to be executed based on the condition at the end of the script so that I can exit the script if the condition is true without executing the anonymous blocks at the end.

Hope so the requirement is clear.
Re: Exit from a sql script based on condition [message #223698 is a reply to message #223695] Sat, 10 March 2007 09:09 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
rolex.mp wrote on Sat, 10 March 2007 14:55
No , The script contains many anonymous blocks so I want to execute few anonymous blocks based on a condition set by the previous anonymous blocks in the same script

So I coded the anonynmous blocks that has to be executed based on the condition at the end of the script so that I can exit the script if the condition is true without executing the anonymous blocks at the end.

Hope so the requirement is clear.


I'm afraid I don't understand that at all.

"No" to what? You use SET TERM OFF to suppress output.

From your description it sounds like your script is like this:

<<Block1>>
BEGIN
various commands;
END;
/

<<Block2>>
BEGIN
various commands;
END;
/

<<Block3>>
BEGIN
various commands;
END;
/

Now depending on the outcome of something in Block1 or Block2, you want it to exit the script silently (no output, errors etc) without executing Block3?

Or, would you want to be able to skip Block2 and continue with Block3?

Or, are the blocks in separate .SQL files that are called conditionally from a shell script?
Re: Exit from a sql script based on condition [message #223714 is a reply to message #223693] Sat, 10 March 2007 11:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
William Robertson wrote on Sat, 10 March 2007 15:50
I still say scripts suck though Wink


Hear hear! Good article!
Previous Topic: Disadvantages of Bulk Collect
Next Topic: AUTHID
Goto Forum:
  


Current Time: Fri Dec 09 11:34:00 CST 2016

Total time taken to generate the page: 0.10604 seconds