That works. And your explanation is perfectly clear. Many thanks.
Vladimir M. Zakharychev wrote:
> 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 - 08:45:51 CDT
