Gary -
The problem is that the select has to return the values into
somewhere.
SELECT blah, blah, blah
INTO outblah, outblah, outblah
FROM table_whatever
....
this type of select can only return ONE row.
A CURSOR still returns ONE ROW at a time.
DECLARE
the cursur......
BEGIN
open the cursur
loop
fetch a row INTO ....
end loop;
close the cursor
end;
Sorry about the psuedo code type stuff but I suspect you'll get
the idea.
Now you can place each returned row into vairables
and return them through parameters in the procedure
but how many rows? you need one variable per column
and a variable (or a PL/SQL table) to collect the multiple
rows.
Believe me there are easier ways!
I got to tell you this is a common issue for folks who have
used Sybase.
What do you plan to do with the returned rows OUTSIDE the
procedure that you can't
do with each row as it is returned INSIDE the procedure?
Think of a procedure as accomplishing a body of work - rather
than a tool to
select rows.
So - bottom line - NO you can't but you shouldn't need to. You
are overlooking the real power of PL/SQL if you think of
it as a query tool.
Becca
> I am getting desperate in trying to implement something that
I have not been
> able to figure out (I have only used sybase till now) and
have not gotten
> much help from a couple Oracle DBA's.
>
> Is it possible to create an oracle stored procedure that will
simply allow
> me to query for multiple rows? I.e. can I execute a stored
procedure that
> might have a few parameters but basically does a 'select c1,
c2, c3 from x'?
> I'm looking for the oracle equivalent of this sybase
procedure:
>
> create proc selectX as
> {
> select c1, c2, c3 from x;
> }
>
> I understand that I will probably need to use cursors
somehow, but I'm new
> to oracle and haven't found a single example anywhere that
can do this. Is
> this possible?
>
> The second thing is that I want to call these procs from a
java app using
> JDBC.
>
> Any help would be greatly, greatly appreciated.
>
> Thanks,
> Gary Mui
> gmui_at_prescientmarkets.com
>
>
>
>
>
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=8226&cus_id=USENET&qtn_id=7349
Received on Mon Dec 13 1999 - 09:41:20 CST
