Re: Multi-use cursor in a package.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/20
Message-ID: <34c62d82.3403714_at_192.86.155.100>#1/1

On Tue, 20 Jan 1998 16:01:19 GMT, mark.woodbury_at_ps.net (Mark Woodbury) wrote:

>I have a not uncommon situation I am trying to program using a
>package.
>
>One of the procedures in the package joins two tables which will
>return a number of columns from both tables. If the second table has
>no records to satisfy the query (this is ok), this is detected and the
>first table is queried, placing zeros in the columns of the second
>table.
>
>Is it possible:
>1.Use a "weak reference" cursor in a package which is defined in the
>specification, and used as a return variable by the procedure.
>
>2. In the procedure , open the cursor using OPEN name FOR SELECT
>query... for both queryies. The cursor layouts are the same.
>
>3. Return the cursor to the caller.
>
>I can get the package to compile and store in the database, but I
>haven't had any luck getting it to execute. Page 171 of the O'Reilly
>Oracle PL/SQL Programming book says you have to have a RETURN on a
>cursor defined in a package specification, which I don't have.
>
>Anyone have any ideas?
>
>Thanks,
>Mark

I think this is what you mean right?

create or replace package demo_pkg
as

    type refCur is REF CURSOR;

    function open_it return refCur;
end;
/

create or replace package body demo_pkg
as

g_cnt number default 1;

function open_it return refCur
is

    l_refcur refCur;
begin

    if ( mod( g_cnt, 2 ) = 0 ) then

        open  l_refCur for
           select ename, job, mgr, dname
             from scott.emp e, scott.dept d
            where e.deptno = d.deptno
              and ename like '%K%';
    else
        open  l_refCur for
           select ename, job, mgr, NULL
             from scott.emp
            where ename like '%B%';

    end if;

    g_cnt := g_cnt + 1;
    return l_refCur;
end;

end;
/

create or replace procedure show_it
as

    l_cur   demo_pkg.refCur;
    l_ename varchar2(255);
    l_job   varchar2(255);
    l_mgr   varchar2(255);
    l_dname varchar2(255);

begin

    l_cur := demo_pkg.open_it;
    loop

        fetch l_cur into l_ename, l_job, l_mgr, l_dname;
        exit when l_cur%notfound;
        dbms_output.put_line( l_ename || ',' || l_job || ',' ||
                                l_mgr || ',' ||  l_dname);

    end loop;
    close l_cur;
end;
/

If you run show_it 2 times you'll see:

BLAKE,MANAGER,7839, PL/SQL procedure successfully completed.

CLARK,MANAGER,7839,accounting
KING,PRESIDENT,,accounting
BLAKE,MANAGER,7839,sales

PL/SQL procedure successfully completed.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 20 1998 - 00:00:00 CET