Re: problem with function which returning cursor

Sadys wrote:
> I have a problem with a function:
>
> function getOrder (in numberOfOrders) return refCursor
> (
> select sequence.nextval into tag; -- we get number value from sequence
>
> update aaa -- we update older than sysdate rows from table using tag value
> set field=id
> where realization_date<sysdate
> and rownum<=numberOfOrder;
>
> open outCursor for -- we get updated rows from aaa table into cursor
> select aaa.*,
> bbb.id
> from aaa,
> bbb
> where aaa.id=bbb.id
> and aaa.field=tag;
>
> return outCursor;
> );
>
> This function dosen't return any rows, althought there are rows
> with realization_date lower than sysdate and for sure join aaa.id and
> bbb.id takes place - where could be the problem?
> Before execut a function I start transaction and after execut function
> transaction is closed.
> Database server dosent throw any exception.
> From my point of view it looks as if the update clause or return cursor
> dosen't work...
>
> Thanks in advance,
>
> Paul.

What you posted is not the DDL used to create the function. Try the following:

SELECT dbms_metadata.get_ddl('FUNCTION', 'GETORDER') FROM dual;

Then post the actual code.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)