Re: Stored Procedure/Function Question

Sybrand
  Thanks for the quick help and pointer to the book. I'll pick up a copy ASAP. In the meantime, when you said "a procedure which returns a REF CURSOR" did you literally mean a "procedure" as opposed to a "function"? It is important for my app that the application code (implemented in VB6 using ADO) doesn't have to change between SQL7 and ORACLE; thus whatever I do in ORACLE should "return" the CURSOR (recordset in ADO terminology) in a way that looks just like SQL7 (or Access). This would seem to rule out returning a value via an OUT parameter since I'm guessing a change in the referencing syntax in the app would result.

Thanks again,
 Bill

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message news:943989603.4466.0.pluto.d4ee154e_at_news.demon.nl...
> 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:40:59 CST