Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g43g2000cwa.googlegroups.com!not-for-mail From: "jeffchirco@gmail.com" Newsgroups: comp.databases.oracle.tools Subject: store results from function into two seperate variables??? Date: 8 Aug 2005 07:58:10 -0700 Organization: http://groups.google.com Lines: 84 Message-ID: <1123513090.067040.164590@g43g2000cwa.googlegroups.com> NNTP-Posting-Host: 64.58.178.11 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1123513096 7933 127.0.0.1 (8 Aug 2005 14:58:16 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 8 Aug 2005 14:58:16 +0000 (UTC) User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g43g2000cwa.googlegroups.com; posting-host=64.58.178.11; posting-account=TXWhoA0AAADENYjbBR7pAw8kNTH5ygnS Xref: dp-news.maxwell.syr.edu comp.databases.oracle.tools:68998 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; end p_storedivreg_hist; When I run it in sqlplus; select p_storedivreg_hist.results(32) from dual; I get: CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 DBDIV DBREG ---------- ---------- 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@inotest> print c; DBDIV DBREG ---------- ---------- 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.