| HOW TO EXIT OUT OF SQLPLUS [message #534204] |
Sun, 04 December 2011 09:11  |
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 #534206 is a reply to message #534204] |
Sun, 04 December 2011 09:21  |
Solomon Yakobson
Messages: 3303
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
