Home » SQL & PL/SQL » SQL & PL/SQL » Create Procedure that returns recordset
Create Procedure that returns recordset [message #37733] Mon, 25 February 2002 21:56 Go to next message
Liquido
Messages: 4
Registered: February 2002
Junior Member
hi, i'm still new of using pl/sql
i wonder is it possible to make a stored procedure that returns recordset ? i'm gonna use the recordset in ASP ..
or maybe somebody here has some experience of creating it and using it in ASP ?
can you give some suggestion and samples please ?

thanks so very much
Re: Create Procedure that returns recordset [message #37735 is a reply to message #37733] Tue, 26 February 2002 02:14 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
use "ref cursor" to return the recordset.

here is an example

create or replace package testpack as
type rc is ref cursor;
end;
/

create or replace function returnrecordset
return testpack.rc as
a testpack.rc;
begin
open a for select * from emp;
return a;
end;
/
Re: Create Procedure that returns recordset [message #37754 is a reply to message #37733] Tue, 26 February 2002 20:18 Go to previous messageGo to next message
Liquido
Messages: 4
Registered: February 2002
Junior Member
Thanks for your reply,
N i want to ask some suggestions
if, i used the cursor to open an sql statement that is given as a parameter of the proc, will it do much better than if i exec the query (plainly-without using the proc) ?
well the query is delivered from the asp code.
some people say that it would be much faster if using stored procedure...
fyi, the query is dynamic, that's why i'm thinking to give it as a parameter for the stored proc.
Please tell me what do you think of my idea, n probably you can share some of yours too..

thank you.
Re: Create Procedure that returns recordset [message #37761 is a reply to message #37733] Wed, 27 February 2002 01:14 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
if u are firing one query , then u should use plainly.

if u are doing lot of processing then u should use proc. as the code do not have to parsed every time and network overhead will be low

it is better idea to use procedure.
Previous Topic: Re: Cummulative SUM
Next Topic: Input Truncate
Goto Forum:
  


Current Time: Thu Mar 28 13:21:38 CDT 2024