Oracle Database
--
Questions
--
Followers
Top Experts
Why can't I use "case when ... then" expression in a procedure?
A simple query
select name, case when p_id=1 then 'food' end, case when p_id=2 then 'beverage' end
from myTable
It works fine. Then I implement this into a procedure (this is the body of a package):
PROCEDURE Get_DayPart_Elem_ByEvent
(
p_ID IN NUMBER,
p_cursor_out IN OUT master.generic_type
)
IS
BEGIN
Open p_cursor_out for
select name, case when p_id=1 then 'food' end, case when p_id=2 then 'beverage' end from myTable
where p_id <10;
End Get_DayPart_Elem_ByEvent;
Then I got this error message while compliling:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current
Any idea?
select name, case when p_id=1 then 'food' end, case when p_id=2 then 'beverage' end
from myTable
It works fine. Then I implement this into a procedure (this is the body of a package):
PROCEDURE Get_DayPart_Elem_ByEvent
(
p_ID IN NUMBER,
p_cursor_out IN OUT master.generic_type
)
IS
BEGIN
Open p_cursor_out for
select name, case when p_id=1 then 'food' end, case when p_id=2 then 'beverage' end from myTable
where p_id <10;
End Get_DayPart_Elem_ByEvent;
Then I got this error message while compliling:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current
Any idea?
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Sorry, forgot the closing quote:
Open p_cursor_out for
'select name, case when p_id=1 then ''food'' end, case when p_id=2 then ''beverage'' end from myTable
where p_id <10';
Open p_cursor_out for
'select name, case when p_id=1 then ''food'' end, case when p_id=2 then ''beverage'' end from myTable
where p_id <10';
Another thing - didn't see that p_id is actually a parameter, not a column from your table... You can either concatenate it in the select query string or use bind variables (better):
Open p_cursor_out for
'select name, case when :p_id=1 then ''food'' end, case when :p_id=2 then ''beverage'' end from myTable
where :p_id <10' USING p_id;
Hope that helps!
Open p_cursor_out for
'select name, case when :p_id=1 then ''food'' end, case when :p_id=2 then ''beverage'' end from myTable
where :p_id <10' USING p_id;
Hope that helps!
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Oracle Database
--
Questions
--
Followers
Top Experts
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.