Re: Stored Procedure/Function Question

You need to use a procedure which returns a REF CURSOR Get Oracle PL/SQL programming by Steve Feuerstein, published by O' Reilly. This book discusses all PL/SQL features in detail with many examples. For initial examples you could also use the web-site of Thomas Kyte, http://osi.oracle.com/~tkyte/

Hth,

--
Sybrand Bakker, Oracle DBA
Bill Cohagan <bill_at_XNOSPAMXteraquest.com> wrote in message news:o9V04.231$Ld.58908_at_newsin1.ispchannel.com...
> I'm using Oracle 8.0.4 PE and am new to Oracle with past experience with
MS
> Access & SQL7. I'm trying to create the equivalent of an Access
> "parameterized query" -- which in SQL7 is done simply with a Stored
> Procedure. The query returns (possibly) multiple rows.
>
> Oracle doesn't seem to like this formulation however; e.g.,
>
> CREATE PROCEDURE foo (arg1 IN Int, arg2 IN Int) IS
> BEGIN
> SELECT tblFoo.* WHERE (tblFoo.field1 = arg1) AND (tblFoo.field2 = arg2);
> END;
>
> [This is how it's done in SQL7 except for minor syntactic differences.]
With
> Oracle this produces a message indicating compilation errors, although
SHOW
> ERRORS then says there are none (even when I explicitly ask for errors on
> this procedure.)
>
> Since Oracle supports FUNCTION I'm guessing that FUNCTION is probably what
I
> need to use rather than PROCEDURE. Of course what's not clear is exactly
> WHAT I should return in the function as a value. Is a CURSOR the right
> thing? Is there a better way to do this in general in Oracle?
>
> Thanks in advance,
> Bill
>
>
>
Received on Tue Nov 30 1999 - 13:18:40 CST