CASE statement in SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 30
Joined: Thu Mar 22, 2012 7:08 pm

CASE statement in SQL

Postby baseballGuy » Thu Jun 07, 2012 8:25 pm

Hi Tim,

Below example of the CASE statement in SQL:

select table_name,
CASE owner
WHEN 'SYS' THEN 'dbms_service.blah;'
WHEN 'SYSTEM' THEN 'dbms_service.DUD'
ELSE 'The owner is another value'
from all_tables;

1. I don't think we can have more than ONE condition in the WHEN, for example: WHEN 'SYSTEM' and 'TEST'. b/c my second and third condition, are the same so I need to add at least one more condition to differentiate the two, how can I do that?

2. how can I call or write the result on the dbms_service to a file and also use the SQLPLUS to execute the result.


Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: CASE statement in SQL

Postby Tim... » Thu Jun 07, 2012 10:36 pm


1) You need a searched case expression to use multiple conditions in the single decision: ... 9i.php#SCE

2) You can only call PL/SQL functions in SQL, not procedures. The return value of the function will be displayed as the column value.

Code: Select all

select table_name,
WHEN 'SYS'  and .... THEN dbms_service.function-name
WHEN 'SYSTEM'  and ... THEN another-function
ELSE 'The owner is another value'
from all_tables;
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest