Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alan.

Asked: November 09, 2001 - 2:24 pm UTC

Last updated: September 15, 2007 - 3:24 pm UTC

Version: 8.0.6.0.0

Viewed 50K+ times! This question is

You Asked


Hi Tom!

I want to use an IF statement in a .SQL with use the BEGIN/END to do something like this (it's just an example):

IF &1 = 1 THEN
SELECT * FROM EMP;
ELSE
SELECT * FROM DEPT;
END IF;

If I enter 1 then SQL*Plus shows this result:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 800 20
7782 CLARK MANAGER 7839 09/06/2081 2548,19 10
7839 KING PRESIDENT 0 17/11/2081 3000 10
....

Otherwise it shows:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


If the only way is by using BEGIN/IF/DBMS_OUTPUT it's bad for me!!!

And another question is how can I exit from a .SQL !?
PS: This question is most valid if there's an answer to the first one!
Like this:

IF &1 = 1 THEN
RETURN;
END IF;

Thanks a lot!

Regards,

Alan

and Tom said...


why is it bad for you? It would look like this:

=====================================================
set autoprint on
variable x refcursor

begin
if &1 = 1 then
open :x for select ename, hiredate from emp where rownum < 5;
else
open :x for select * from dept;
end if;
end;
/

whenever sqlerror exit
exec if &1 = 1 then raise_application_error( -20000, 'Goodbye' ); end if;
whenever sqlerror continue
=====================================================

Now when I run this I see:

scott@ORA717DEV.US.ORACLE.COM> @test 0
old 2: if &1 = 1 then
new 2: if 0 = 1 then

PL/SQL procedure successfully completed.


DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


PL/SQL procedure successfully completed.

scott@ORA717DEV.US.ORACLE.COM> @test 1
old 2: if &1 = 1 then
new 2: if 1 = 1 then

PL/SQL procedure successfully completed.


ENAME HIREDATE
---------- ---------
A 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81

BEGIN if 1 = 1 then raise_application_error( -20000, 'Goodbye' ); end if; END;

*
ERROR at line 1:
ORA-20000: Goodbye
ORA-06512: at line 1


Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production


which is pretty much what you wanted...

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cool, thanks

A reader, November 09, 2001 - 5:30 pm UTC


Great!

Alan Rech, November 12, 2001 - 6:31 am UTC

Thanks Tom!!!

The answer to the fisrt question is exactly what I need...
But in the second, it'll not work when you have a script that mixes PL/SQL with SQL*Plus commands...

Best regards,

Alan

Tom Kyte
November 12, 2001 - 9:38 am UTC

what I do then is something like:


set serveroutput on
set feedback off
spool temp.sql
begin
if ( something )
then
dbms_output.put_line( 'host ..... ' );
else
dbms_output.put_line( 'prompt nothing to do here, skipping host' );
end if;
end;
/
spool off
@temp



Re: Follow-up

Alan Rech, November 12, 2001 - 11:38 am UTC

This is a good alternative! I'll try it...

Thanks again.

[]'s

Alan

Equivalent in PL/SQL

VA, July 16, 2005 - 9:32 pm UTC

How can I get the equivalent of "whenever sqlerror continue" in a PL/SQL stored procedure?

I have a SQL "script" that I am trying to convert to a PL/SQL stored procedure. It does lots of create table, drop tables, add column, drop column, etc.

It could be aborted by the user at any time, so its state is not really known. So, when it is restarted, it might try to drop a table that doesnt exist, create one that exists, etc.

Short of wrapping each DDL statement in a 'when others then null', how can I just ignore such errors like 'whenever sqlerror continue'?

Thanks

Tom Kyte
July 17, 2005 - 8:55 am UTC

well, that could be quite dangerous couldn't it.

but in short, very very much fortunately, there is not any "please just ignore errors, go ahead and do the damage" mode in the programming language that is plsql.

You would have to wrap statements you anticipate failing with exception blocks.

Jagadeesh Tata, July 18, 2005 - 6:03 am UTC

Good...

How to exit from if...

A reader, September 11, 2007 - 8:29 am UTC

Tom,

I try to write a script with anonymous plsql block that check database version and if the database version > 8 it does some selects against v$views that exist only in >8 db otherwise exit with a message "Option is not available in this database version".
The problem is if I write
if ... then ... else and place my select statement for 9i/10G database in the else...Oracle brings object not exist error.
What I want is to see only the output "Option not available" and not the error text (if I set whenever sqlerror exit) in case of 8 db and go to my select statements in case of 9i/10g

Tom Kyte
September 15, 2007 - 3:24 pm UTC

use dynamic sql of course.


if (version >8)
then
   open cursor c for 'select * from v$whatever';
   loop
       fetch c into .....;  
       exit when c%notfound;
       .....
   end loop;
   close c;
else
   dbms_output.put_line( 'you lose' );
end if;

Thank you.

A reader, February 12, 2015 - 2:20 am UTC

Thanks for having this up. Just wanted to let you know that it is still of use.

Still useful :)

A reader, April 03, 2022 - 5:47 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.