store results from function into two seperate variables???

From: <>
Date: 8 Aug 2005 07:58:10 -0700
Message-ID: <>

I have created a package the returns a ref cursor. The package is as follows:
CREATE OR REPLACE PACKAGE p_storedivreg_hist is

  • Author : JEFFC
  • Created : 6/20/2005 9:20:18 AM
  • Purpose : Get division history for a store based on a date
  • Public type declarations type resultset is REF CURSOR;
  • Public function and procedure declarations function results(dbctr_in NUMBER) return resultset;

end p_storedivreg_hist;

create or replace package body p_storedivreg_hist . is

  • Function and procedure implementations function results(dbctr_in NUMBER) return resultset IS rset p_storedivreg_hist.resultset; BEGIN OPEN rset FOR SELECT c.dbdiv, r.dbctr dbreg FROM centers c, centers d, centers r WHERE c.dbctr = dbctr_in AND c.dbdiv = d.dbctr AND d.dbdiv = r.dbctr AND c.dbdate = (SELECT MAX(dbdate) FROM centers WHERE dbdate <= SYSDATE AND dbctr = c.dbctr) AND d.dbdate = (SELECT MAX(dbdate) FROM centers WHERE dbdate <= SYSDATE AND dbctr = d.dbctr) AND r.dbdate = (SELECT MAX(dbdate) FROM centers WHERE dbdate <= SYSDATE AND dbctr = r.dbctr);

RETURN rset;


end p_storedivreg_hist;

When I run it in sqlplus;
select p_storedivreg_hist.results(32) from dual;

I get:
---------- ----------
1683 2943

1 row selected.

1 row selected.

Or I can run it by:
SQL> var c refcursor
SQL> exec :c := p_storedivreg_hist.results(32);

PL/SQL procedure successfully completed.

op_user_at_inotest> print c;

---------- ----------
1683 2943

My question is, is there a way to store the results into two seperate varaibles in a function or a procedure? Thanks for all help. Do in select p_storedivreg_hist.results(32) into x,y from dual; Does not work. Received on Mon Aug 08 2005 - 16:58:10 CEST

Original text of this message