Re: Can a procedure contain only a SELECT statement?

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 18 Mar 2010 16:30:37 +0000 (UTC)
Message-ID: <pan.2010.03.18.16.30.36_at_email.here.invalid>

On Thu, 18 Mar 2010 13:17:23 +0000, Mladen Gogala wrote:

> On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote:
>

>> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
>>      myrec aTableName%ROWTYPE;
>> begin
>> select  *
>> into myrec
>>   from  aTableName
>>  where  aColumn = aLikeValue
>>  and rownum < 2;
>> end;
>> /

>
> Nope. This is the right answer:
>
> create or replace function aSimpleSelect (a_like_value char(4)) return
> ref cursor as
> v_csr ref cursor;
> begin
> open v_csr for
> select *
> from aTableName
> where aColumn = a_like_value;
> return(v_csr);
> end;
> /

This is the right answer that compiles:

create or replace package ddf
as
type refcsr is ref cursor;
function ssel(a_empno number) return refcsr; end ddf;
/
create or replace package body ddf
as
function ssel(a_empno number) return refcsr is
v_csr refcsr;
begin
open v_csr for
select * from emp
where empno=a_empno;
return(v_csr);
end;
end ddf;
/

-- 
http://mgogala.byethost5.com

Received on Thu Mar 18 2010 - 11:30:37 CDT