Re: Returning stored procedure results to query tool

In article <s68.80.2DD12473_at_ornl.gov>, <s68_at_ornl.gov> writes:
> I've discovered Oracle handles procedures quite a bit differently. It seems
> like Oracle procedures don't want to return results to my query tool. Even
> using PS/SQL, it was hard to get Oracle to return any results of a select
> statement [...]
> What I'd really like to do is get Oracle to return results of a stored
> procedure like Sybase does. Does anyone have any tricks or suggestions.

as you discovered, stored pl/sql procedures cannot return result sets. you can work around this by writing a package that contains two objects: a cursor for your select, and a procedure that uses that cursor.

the trick you use is: call the procedure with one IN argument and a bunch of OUT arguments (the result column values); if the cursor is closed, the procedure opens it using the IN argument (in the where clause); if the cursor is open, fetch a row into the OUT arguments; if there is no data left, close the cursor.

if you need more than this hint, write to me for a working sample pl/sql proc. i recall that you may also find a similar sample pl/sql code in the Pro*C examples, if you have them. Received on Thu May 12 1994 - 04:40:47 CEST