Re: please help! oracle stored procedure and jdbc

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Wed, 3 Nov 1999 10:20:41 +0100
Message-ID: <941620886.1572.0.pluto.d4ee154e@news.demon.nl>

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! Of course there are restrictions: no dynamic sql is allowed with ref cursors.

Hth both of you,

--
Sybrand Bakker, Oracle DBA
<victorme_at_my-deja.com> wrote in message news:7votg3$fkt$1_at_nnrp1.deja.com...
> In article <4iCT3.18$Nn5.500_at_client>,
> "Gary T. Mui" <gmui_at_prescientmarkets.com> wrote:
> > I am getting desperate in trying to implement something that I have
> not been
> > able to figure out (I have only used sybase till now) and have not
> gotten
> > much help from a couple Oracle DBA's.
> >
> > Is it possible to create an oracle stored procedure that will simply
> allow
> > me to query for multiple rows? I.e. can I execute a stored procedure
> that
> > might have a few parameters but basically does a 'select c1, c2, c3
> from x'?
> > I'm looking for the oracle equivalent of this sybase procedure:
> >
> > create proc selectX as
> > {
> > select c1, c2, c3 from x;
> > }
> >
> > I understand that I will probably need to use cursors somehow, but I'm
> new
> > to oracle and haven't found a single example anywhere that can do
> this. Is
> > this possible?
> >
> > The second thing is that I want to call these procs from a java app
> using
> > JDBC.
> >
> > Any help would be greatly, greatly appreciated.
> >
> > Thanks,
> > Gary Mui
> > gmui_at_prescientmarkets.com
> >
> >
> You can
> declare C1 Cursor
> as select a1,b1,c1 from x;

>


>         declare SumA1 number(10);

> BEGIN
> SumA1 := 0;
> /* For every record */

>


>   FOR C1rec in C1 LOOP


>       SumA1 := SumA1 + C1rec.a1    /* C1rec.a1,C1rec.b1,C1rec.c1 current


>                                       record values*/

> END LOOP;

>

> END;

>
>

> In Oracle Strored Procedure you can not fetch information to the default
> output , you can select information into the variables, if you know,that
> only one record will be selected or work with loop.

>

> declare a number(10);
> begin
> select a1
> into a
> from x;
> end;

>
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Nov 03 1999 - 03:20:41 CST