Home » Other » Client Tools » HOW TO EXIT OUT OF SQLPLUS
HOW TO EXIT OUT OF SQLPLUS [message #534204] Sun, 04 December 2011 09:11 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

I have a scenario where I want to exit out of SQLPLUS.

SQL> declare
2 x number(10) :=1;
3 y number(10) :=2;
4 begin
5 if x=y then
6 dbms_output.put_line('x is equal to y');
7 else
8 dbms_output.put_line('x is not equal to y');
9 exit; /* What should I use here */
10 end if;
11 end;
12 /
exit;
*
ERROR at line 9:
ORA-06550: line 9, column 2:
PLS-00376: illegal EXIT statement; it must appear inside a loop
ORA-06550: line 9, column 2:
PL/SQL: Statement ignored

Plese help me out of this.
Regards,

Anil
Re: HOW TO EXIT OUT OF SQLPLUS [message #534205 is a reply to message #534204] Sun, 04 December 2011 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: HOW TO EXIT OUT OF SQLPLUS [message #534206 is a reply to message #534204] Sun, 04 December 2011 09:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
There is PL/SQL statement EXIT and SQL*Plus command EXIT. PL/SQL statement EXIT is used within LOOP statement in PL/SQL block. Since your EXIT is within PL/SQL block it is interperted by PL/SQL as PL/SQL statement and since it is not used within LOOP statement an error is raised. Use PL/SQL statement RETURN to exit PL/SQL block. Then add SQL*Plus command EXIT after PL/SQL block:

declare
    x number(10) :=1;
    y number(10) :=2;
begin
    if x=y
      then
        dbms_output.put_line('x is equal to y'); 
      else
        dbms_output.put_line('x is not equal to y'); 
        return;
    end if;
end;
/
exit


SY.
Re: HOW TO EXIT OUT OF SQLPLUS [message #534207 is a reply to message #534204] Sun, 04 December 2011 09:39 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot use a SQL*Plus command inside a PL/SQL block.
SQL*Plus is a CLIENT program.
PL/SQL executes on the SERVER.

If you want to exit SQL*Plus you can use WHENEVER SQL*Plus command BEFORE sending PL/SQL block:

SQL> whenever sqlerror exit;
SQL> declare
  2     x number(10) :=1;
  3     y number(10) :=2;
  4  begin
  5   if x=y then
  6      dbms_output.put_line('x is equal to y');
  7    else
  8      raise_application_error(-20000, 'x is not equal to y');
  9   end if;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-20000: x is not equal to y
ORA-06512: at line 8


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: Continue sql script: yes or no
Next Topic: Notepad++
Goto Forum:
  


Current Time: Fri Mar 29 06:05:15 CDT 2024