Re: please help! oracle stored procedure and jdbc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 03 Nov 1999 08:35:00 -0500
Message-ID: <3jkgOFqP9FLji7+KGh9=SfB8nmlb@4ax.com>

A copy of this was sent to "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> (if that email address didn't require changing) On Wed, 3 Nov 1999 10:20:41 +0100, you wrote:

>That's the half story. You can return a cursor as a procedure parameter as
>long as you use a REF CURSOR.
>You can declare such a cursor in sqlplus VARIABLE declaration, call your
>procedure, and PRINT the cursor variable afterwards. Oracle changes faster
>than you think!

how prophetic!

>Of course there are restrictions: no dynamic sql is allowed
>with ref cursors.
>

until 8i of course :)

tkyte_at_8i> variable r refcursor
tkyte_at_8i> 
tkyte_at_8i> begin
  2          open :r for 'select ' || ' ename ' || ' from ' || ' emp';

3 end;
4 /

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> print r

ENAME

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 14 rows selected.

>Hth both of you,

--
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 Wed Nov 03 1999 - 07:35:00 CST