Path: news.easynews.com!easynews!cyclone2.usenetserver.com!usenetserver.com!news.maxwell.syr.edu!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn
From: Thomas Kyte <tkyte@us.oracle.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: help! returning filtered recordsets from oracle stored Proc
Date: 10 Oct 2001 06:10:16 -0700
Organization: Oracle
Lines: 67
Message-ID: <9q1hbo0erf@drn.newsguy.com>
References: <9ptjfn$b54$1@news.vsnl.net.in>
NNTP-Posting-Host: p-552.newsdawg.com
X-Newsreader: Direct Read News 2.90
Xref: easynews comp.databases.oracle.server:120813
X-Received-Date: Wed, 10 Oct 2001 07:29:13 MST (news.easynews.com)

In article <9ptjfn$b54$1@news.vsnl.net.in>, "angshu" says...
>
>Hi All,
>
>I want to write a stored proc in Oracle8i  which will return
>me recordsets. I think I can do it in a couple of ways 1)
>Reference cursor
>2) By creating a package in which I can declare the out
>parameters as "Table of DataType". And returning the values
>through a call to procedure of the package, right? If I use
>the 2nd approach I can't use a select statement to get
>recordsets .. Can I?
>
>But what I want to do is that I want to write a stored
>procedure from which I will retrieve recordsets but only
>after filtering the innitial recordsets by certain
>conditions ..
>
>say if in the stored proc I get the initial recordset as
>-----------
>Col1 Col2
>----------
>1    2000
>2    5002
>3    2394
>4   1309
>....
>.....
>----------
>
>Now this recordset will further undergo many filtering
>processes depending upon values fetched through many values
>by other SQLs in the procedure ... and finally  what I would
>like to get is   ...
>-----------
>Col1 Col2
>----------
>1    2000
>4   1309
>----------
>
>And I would like to get the resultant recordsets by just
>calling a single SQL statement like
>select * from MyStoredProc( .. parameters );
>
>Is this possible anyway? Can show me the light or mention
>where I can get to see any light?

see
http://groups.google.com/groups?q=pipe+interbase+group:comp.databases.oracle.*+author:tkyte%40us.oracle.com&hl=en&rnum=1&selm=9pcksh012bn%40drn.newsguy.com

for how to do this.


>THT
>regards
>~angshu
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp