Re: Help with Anonymous block that returns a cursor


<brad.browne@xxxxxxxxx> wrote in message
news:1143604555.400344.241500@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
:
: 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


.

Relevant Pages

  • Re: nested cursor declarations through dynamic sql
    ... SQL Server MVP ... I need to get a list of file names from an Excel spreadsheet. ... > declared a cursor that uses opendatasourceto get the file names out ... > -- this will declare the cursor for the outer loop (that gets the file ...
    (microsoft.public.sqlserver.programming)
  • Re: Declare cursor problem in ISeries
    ... The cursor declaration is as follows: ... SQL statement is not allowed for one of the following reasons: ... DECLARE PROCEDURE, DESCRIBE, GET DIAGNOSTICS, SIGNAL, RESIGNAL, and ... The version information of Iseries is: ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL Server steht auf der Bremse
    ... DECLARE targetID_cursor CURSOR LOCAL FORWARD_ONLY FOR ... INSERT INTO .... ... skaliert der SQL Server über alle 4 Prozessoren. ...
    (microsoft.public.de.sqlserver)
  • Re: Declare cursor problem in ISeries
    ... When I try to declare the cursor in Iseries Run SQL scripts tool I get ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL Taking too long .
    ... SQL Server MVP ... Am creating a cursor from a table A which has about> 120,000 record. ... > DECLARE @TranBudget VARCHAR ... > declare @budgetTotalRows int ...
    (microsoft.public.sqlserver.programming)