<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