Re: Help with Anonymous block that returns a cursor
- From: Graham Wallace <GrahamWallace@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 05:35:00 GMT
brad.browne@xxxxxxxxx wrote:
Hi all,There are two problems I can see with this code.
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
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
.
- References:
- Help with Anonymous block that returns a cursor
- From: brad . browne
- Help with Anonymous block that returns a cursor
- Prev by Date: Help with Anonymous block that returns a cursor
- Next by Date: Re: inexpensive SAN for Linux 10gR2 RAC
- Previous by thread: Help with Anonymous block that returns a cursor
- Next by thread: Re: Help with Anonymous block that returns a cursor
- Index(es):
Relevant Pages
|
