Refcursor as parameter to function

From: <pbewig_at_swbell.net>
Date: 13 Sep 2006 14:13:22 -0700
Message-ID: <1158182002.339272.110340@i42g2000cwa.googlegroups.com>

I am unable to pass a refcursor to a function. A simplified version of what I am trying to do is shown below. I begin by creating a function that returns a refcursor containing a single field:

    create or replace function bewig

        return sys_refcursor
    is

        q sys_refcursor;
    begin

        open q for
            select spriden_pidm as pidm
            from   spriden
            where  spriden_change_ind is null
            and    upper(spriden_last_name) = 'BEWIG';
        return q;

    end bewig;

Then a second function takes that refcursor as a parameter and inserts all its records into a table:

    create or replace function gf_name_addr (

        parm_query in sys_refcursor )
        return number
    is
        type pidm_record is record(pidm number(8));
        query_rec pidm_record;
        result_count number := 0;
    begin
        loop fetch parm_query into query_rec;
             exit when parm_query%notfound;
             insert into gt_name_addr_rc (pidm)
                 values (query_rec.pidm);
             result_count := result_count + 1;
        end  loop;
        commit;
        close parm_query;
        return result_count;

    end gf_name_addr;

When I call the function I get an 'invalid cursor' error:

    SQL> variable x number
    SQL> call gf_name_addr(bewig()) into :x;     call gf_name_addr(bewig()) into :x

         *
    ERROR at line1:
    ORA-01001: invalid cursor
    ORA-06512: at "PBEWIG.GF_NAME_ADDR", line 9

As I said, this question is simplified. In its original form, gt_name_addr will go on to update the gt_name_addr temporary table with several additional fields, then pass it on to yet a third function. I must pass the refcursor as the return value of a function because I expect to have many functions, each creating a refcursor, that are all used as input to the gf_name_addr function; in all cases, the type of the refcursor will be a single field containing a number(8).

Can someone explain what the error message means? And what is the proper way to do what I want?

Many thanks,

Phil Received on Wed Sep 13 2006 - 16:13:22 CDT