Return result set from stored procedure, possible ? 
Author Message
 Return result set from stored procedure, possible ?

Hello

This is propably a FAQ, but since I couldn't find it there, I ask here.

Is it possible to let stored procedures return a result set?
I have used sybase earlier, and it works there...

something like:

CREATE PROCEDURE SEL_ALL AS
BEGIN
        SELECT COL_A, COL_B, COL_C FROM TABLE_A;
END;
/

so that when you call it, it would be just like running the SELECT
directly.

When I tried, I got compilation errors, and Oracle wanted me to select
INTO some variable...

The purpose is to avoid embedding SELECT statements in external programs,
and keep the SELECTs inside Oracle, so they can be easily changed...

What do you suggest ?

Regards
--
Gunnar Bj?rgum

Norway



Wed, 18 Jun 1902 08:00:00 GMT
 Return result set from stored procedure, possible ?

It is a FAQ, and I just responded to it in a different thread in this group.
This newsgroup archives are at www.deja.com, please search before posting
questions, that have been answered at least a 100 times.

Regards,

Sybrand Bakker, Oracle DBA


Quote:
> Hello

> This is propably a FAQ, but since I couldn't find it there, I ask here.

> Is it possible to let stored procedures return a result set?
> I have used sybase earlier, and it works there...

> something like:

> CREATE PROCEDURE SEL_ALL AS
> BEGIN
> SELECT COL_A, COL_B, COL_C FROM TABLE_A;
> END;
> /

> so that when you call it, it would be just like running the SELECT
> directly.

> When I tried, I got compilation errors, and Oracle wanted me to select
> INTO some variable...

> The purpose is to avoid embedding SELECT statements in external programs,
> and keep the SELECTs inside Oracle, so they can be easily changed...

> What do you suggest ?

> Regards
> --
> Gunnar Bj?rgum

> Norway



Wed, 18 Jun 1902 08:00:00 GMT
 Return result set from stored procedure, possible ?
I am not sure why you think it is more portable or easier to maintain by
using a stored procedure to just hide:
select ... from ...

Properly written code would use host variables etc. which will get you
speed.  I have seen people write code to make table abstractions so one
doesn't have to write SQL, but you just end up specifying your set stuff in
a different way.
Jim


Quote:
> Hello

> This is propably a FAQ, but since I couldn't find it there, I ask here.

> Is it possible to let stored procedures return a result set?
> I have used sybase earlier, and it works there...

> something like:

> CREATE PROCEDURE SEL_ALL AS
> BEGIN
> SELECT COL_A, COL_B, COL_C FROM TABLE_A;
> END;
> /

> so that when you call it, it would be just like running the SELECT
> directly.

> When I tried, I got compilation errors, and Oracle wanted me to select
> INTO some variable...

> The purpose is to avoid embedding SELECT statements in external programs,
> and keep the SELECTs inside Oracle, so they can be easily changed...

> What do you suggest ?

> Regards
> --
> Gunnar Bj?rgum

> Norway



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. returning result sets from stored procedures

2. Returning result sets in oracle stored procedure (OLEDB)

3. return multiple result sets from a stored procedure

4. Can't update result set returned from stored procedure using RDO

5. Returning result set from function to stored procedure is very slooooooooow

6. best way to return a paged result set from a stored procedure

7. Returning a result set from a Stored Procedure

8. returning result sets from stored procedures

9. How not to return multiple result sets from a stored procedure

10. Stored Procedure/Multiple Return Results/Set Statement

11. return result set from stored procedure

12. return result set from stored procedure


 
Powered by phpBB® Forum Software