Re: stored procedures in oracle

This is the most frequently asked question by people exposed to sqlserver and it has been really answered *numerous* times. You need to use a REF CURSOR as in out parameter to your procedure. Refer to the Oracle PL/SQL documentation on that topic to the book on Pl/SQL by Steven Feuerstein or to http://osi.oracle.com/~tkyte subject Resultsets.

Regards,

Sybrand Bakker, Oracle DBA

<emmet_rc_at_yahoo.com> wrote in message
news:92irko$jin$1_at_localhost.localdomain...
> Can you give an example of the proper syntax (or point out the errors in
> the code below). I'm trying to return a multi-row resultset to an
> application that should be able to handle any ODBC data source. My tests
> with MS SQL are working. The stored procedure code in that case is
> create procedure TestSP
> as
> Select * From TestTable
>
> I'm using the schema manager to create/edit the oracle stored procedures.
> The code for my SP (which compiles in the schema manager, but does not
> provide a usable resultset to my application) is
> Create Procedure TestSP
> as
> CURSOR ResSet IS
> SELECT * FROM TestTable;
> begin
> OPEN ResSet;
> end;
>
> I've also tried create an Oracle function, also using schema manager, but
> could not get one to compile.
>
> Any help would be greatly appreciated.
> Thanks.
>
Received on Fri Dec 29 2000 - 15:24:11 CST