create procedure ora_sp_decode2 (job_level out
varchar2) as
begin
select DECODE(job_lvl,1,'level 1',2,'level 2','Unknown
level') into job_level from employee where job_id>0;
end;
create procedure ora_sp_decode2 (out job_level TEXT )
begin
select CASE job_lvl
WHEN 1 THEN 'level 1
WHEN 2 THEN 'level 2'
ELSE 'Unknown level' end
into job_level from employee where job_id>0 ;
end ;