Re: Refcursor as parameter to function

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 14 Sep 2006 00:30:07 -0700
Message-ID: <1158219007.544032.162210@e63g2000cwd.googlegroups.com>

pbewig_at_swbell.net wrote:
> 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?
>

The error message means that REF CURSOR variable doesn't point to a valid query work area when you try to fetch from it. The following works:

SQL> var x number
SQL> exec :x := gf_name_addr(bewig());

PL/SQL procedure successfully completed.

I think the problem is that CALL is an SQL command, and SQL engine doesn't pass REF CURSORs returned from functions properly. This doesn't work:

SELECT gf_name_addr(bewig) FROM DUAL;

throwing the same ORA-1001. The following works, though:

SELECT gf_name_addr(CURSOR(select spriden_pidm from ...)) FROM DUAL;

Not sure if this is a defect or as designed, probably the latter.

Also, since your queries always have one column of NUMBER(8) type, there's no need to declare a record type, you can fetch right into a NUMBER(8) variable, like this:

create or replace function gf_name_addr (

         parm_query in sys_refcursor )
         return number
     is
         l_pidm NUMBER(8);
         result_count number := 0;
     begin
         loop fetch parm_query into l_pidm;

    ....

This is irrelevant to the problem, but optimizes resource usage a bit.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Sep 14 2006 - 02:30:07 CDT