Re: Help with Anonymous block that returns a cursor

<brad.browne_at_gmail.com> wrote in message news:1143604555.400344.241500_at_e56g2000cwe.googlegroups.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 Received on Wed Mar 29 2006 - 05:19:50 CST