Home » SQL & PL/SQL » SQL & PL/SQL » SWITCH-CASE (Oracle 9)
SWITCH-CASE [message #377528] Tue, 23 December 2008 05:53 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi,

Can somebody provide examples of switch and case in oracle.

Regards
Shaksing
Re: SWITCH-CASE [message #377534 is a reply to message #377528] Tue, 23 December 2008 06:36 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
use google.you will find n number of examples..
Re: SWITCH-CASE [message #377536 is a reply to message #377528] Tue, 23 December 2008 06:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no Switch statement/command in Oracle (unless you're thinking about forcing a log file change).

CASE is documented and has examples here
Re: SWITCH-CASE [message #377616 is a reply to message #377528] Tue, 23 December 2008 23:10 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually i want to perform something like this.


STATE is a column from table_name which is varchar2(1) and can have value E, P, T or F


CREATE OR REPLACE PROCEDURE TEST
IS
STATE VARCHAR2(1);
STEP NUMBER ;



BEGIN
	 select STATE from table_name where step number =1;
  	
  	 SWITCH (STATE)
  	 DO
  CASE 'E': 
   	 DBMS_OUTPUT.PUT_LINE ('THE PROCESS IS RUNNING FROM ANOTHER TERMINAL');

  CASE 'P': 
   	       Some operation
	       ---if-else and operation


  CASE 'T': 
   	   	   Some operation
	       ---if-else and operation
   
CASE 'F': 
   	 	  Some operation
	       ---if-else and operation

   DEFAULT:
   DO END

END IF;


EXCEPTION
WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'AN ERROR WAS ENCOUNTERED - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/


I tried to found it in google but that was not working.

http://stanford.edu/dept/itss/docs/oracle/10g/olap.101/b10339/x_stddev006.htm

Please provide your inputs here.

[Updated on: Tue, 23 December 2008 23:22]

Report message to a moderator

Re: SWITCH-CASE [message #377618 is a reply to message #377528] Tue, 23 December 2008 23:33 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Or i should go for If elsif elsif else endif ?
Re: SWITCH-CASE [message #377619 is a reply to message #377616] Tue, 23 December 2008 23:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can find CASE statement's help on net easily.
By the way, you can use case statement like this

case (select state from <table>)
when 'E' then
    <statements>
when 'P' then
    <statements>
when 'T' then
    <statements>
when 'F' then
    <statements>
else
    <statements>
end case

Re: SWITCH-CASE [message #377636 is a reply to message #377619] Wed, 24 December 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Wed, 24 December 2008 06:37
You can find CASE statement's help on net easily.
By the way, you can use case statement like this

case (select state from <table>)
when 'E' then
    <statements>
when 'P' then
    <statements>
when 'T' then
    <statements>
when 'F' then
    <statements>
else
    <statements>
end case



It is better to
1/ Post a link to the documentation as JRowbottom did
2/ Post something that is correct and that you have tested:
SQL> begin
  2    case (select dummy from dual)
  3     when 'X' then null;
  4     when 'Y' then null;
  5    end case;
  6  end;
  7  /
  case (select dummy from dual)
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternativ
ORA-06550: line 3, column 4:
PLS-00103: Encountered the symbol "WHEN" when expecting one of the following:
; return returning and or
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "END"

Regards
Michel

Re: SWITCH-CASE [message #377659 is a reply to message #377616] Wed, 24 December 2008 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's from an Oracle OLAP document. OLAP is a seperate product that is not part of the core database.

Did you read that link I provided that contained a description and examples of how to use CASE?
Re: SWITCH-CASE [message #377660 is a reply to message #377528] Wed, 24 December 2008 02:11 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah JRowbottom i read that and able to execute my procedure now.

Thanks
shaksing
Re: SWITCH-CASE [message #377707 is a reply to message #377636] Wed, 24 December 2008 07:29 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Michel sir for showing me my mistake.

I will keep in mind.

regards,
Delna
Re: SWITCH-CASE [message #377775 is a reply to message #377616] Thu, 25 December 2008 01:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shaksing wrote on Wed, 24 December 2008 06:10
Actually i want to perform something like this.
...
EXCEPTION
WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'AN ERROR WAS ENCOUNTERED - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/



Word of warning: don't do this.
It adds nothing to your code, it only obfuscates the real location where the error was raised.
Previous Topic: Sent attachment with pl/sql as a mail with ( mulitpel recepents )
Next Topic: PLS-00201: identifier 'LIDS.NEXT' must be declared and PLS-00201: identifier 'LIDS.DELETE' must be
Goto Forum:
  


Current Time: Mon Dec 05 03:01:06 CST 2016

Total time taken to generate the page: 0.07104 seconds