Re: Stored Proc w/Multiple Result Sets?

Gary Fidler <gfidler_at_aeq.com> wrote in article <5vk51r$521$1_at_news-2.csn.net>...
> I am trying to write a simple stored procedure which returns multiple
> result sets. I have done this on Sybase servers and wish to do the same
 on
> Oracle 7.*. The server whines about needing an INTO clause.
>
> What am I doing wrong?
>
> My example is:
>
> create or replace procedure username.sp_test
> (
> Arg1 varchar2,
> Arg2 varchar2
> )
> AS
> BEGIN
> select
> field1,
> field2,
> field3
> from
> username.table1
> where
> field1 = Arg1 and
> field2 = Arg2;
>
 

> END;
>
> Thanks
> Gary
>
>

When ever you are using Select statements in PL/SQL the syntax is different.

Use the following code;

Select col1, col2, .... into variable1, variable2, ... from ...
where ...;

This is assuming your select statement is going to return one and only one row. If it returns more than one row you might want to use a cursor, else it will give you an error saying exact fetch returned more than one row!

Have fun with PL/SQL

Sreeni.

-- 
========================================
Sreeni Karpurapu 
DBA
Miracle Software Systems
========================================