Re: Help with Anonymous block that returns a cursor

brad.browne_at_gmail.com wrote:
> Hi all,
>
> I am trying to write an Anonymous block that will return a cursor so
> that I can run this SQL via ODBC and it will return a recordset. I am
> unfamiliar with how I should declare this function so that it will be
> recognised ... at the moment I get an error saying: "'SP_GET_CURSOR' is
> not a procedure or is undefined". Is there something simple that I am
> missing to get this to work ?
>
> DECLARE
> TYPE ref_cursor IS REF CURSOR;
>
> FUNCTION sp_get_cursor RETURN ref_cursor
> IS my_cursor ref_cursor;
> BEGIN
> OPEN my_cursor FOR
> SELECT pr_view_pfi,propnum FROM MapXRef
> WHERE pr_view_pfi = '2783929';
> RETURN my_cursor;
> END;
>
> BEGIN
> sp_get_cursor();
> END;
>
> Regards,
> Brad
>

There are two problems I can see with this code.

1. The problem with the sp_get_cursor call. Between your main begin/end, you need to call the function thus:

ref_cursor := sp_get_cursor();

2) The anonymous block will not be stored in the database. You will need to strip it out and store it as a standalone function or (my preference)   procedure. Even better would be to use a package.

Remember to close the cursor in the calling program once you are finished with it.

Graham Received on Tue Mar 28 2006 - 23:35:00 CST