Re: how to return a cursor in a PL/SQL procedure or function ?

Try this example. It returns all the ids in tbl via an out parameter.

Michael Feldman
mfeldman_at_netcom.com

CREATE OR REPLACE PACKAGE pkg as

    TYPE idList IS TABLE OF tbl.id%TYPE

        INDEX BY BINARY_INTEGER;     PROCEDURE getIds

        (ids OUT idList);

END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg AS

	CURSOR idC IS
		SELECT id
        FROM  tbl;

    PROCEDURE getIds
        (ids    OUT idList) AS
        rows BINARY_INTEGER;
    BEGIN
        rows := 0;
        FOR idRec IN idC LOOP
            rows := rows + 1;
            ids(rows)   := idRec.id;
        END LOOP;

END pkg;
/

In article <3at2tv$nna_at_godot.cc.duq.edu>, SYJERRY_at_duq3.cc.duq.edu writes:
> I recently posted a question if it is possible to return multiple
> rows in a PL/SQL procedure or function and I got two responses
> saying that it can be done using cursors, but did not get any sample
> code.
>
> I tried doing it but got compilation errors.
>
> I did a test script that will return all cities in a given state.
> my script is as follows
>
> create or replace package Test_Pack is
> Function Test(state sys.cities.cty_sta_code%type)
> Return cursor;
> end Test_Pack;
Received on Wed Nov 23 1994 - 19:05:11 CET