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

brad.browne@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

<brad.browne@gmail.com> wrote in message
news:1143604555.400344.241500@e56g2000cwe.googlegr oups.com...
:
: 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
:

anonymous blocks don't 'return' anythinng, but they can reference host
variables

here's a real simple example in SQL*Plus, showing the refcursor variable
declared in the host environment (SQL*Plus in this case) and being
referenced in the anonymous block:

SQL> var rc refcursor
SQL> begin
2 open :rc for 'select * from all_users';
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> print rc

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
....

++ mcs