help! returning filtered recordsets from oracle stored Proc

From: angshu <angshu_at_agnisoft.com>
Date: Mon, 8 Oct 2001 20:52:14 +0530
Message-ID: <9ptjfn$b54$1@news.vsnl.net.in>

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?
THT
regards
~angshu Received on Mon Oct 08 2001 - 10:22:14 CDT