Re: Stored Procedure/Function Question

A copy of this was sent to "Bill Cohagan" <bill_at_XNOSPAMXteraquest.com> (if that email address didn't require changing) On Tue, 30 Nov 1999 12:53:02 -0600, you wrote:

>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
>
>

see the url in my signature for a howto on returning result sets from stored procedures...

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 30 1999 - 13:28:21 CST