Re: Stored Proc w/Multiple Result Sets?

 My intent here is to store database logic in stored procs on the server to encapsulate it from the rest of the business logic in my client application. My application is written in Java so I am using JDBC drivers to access data.

It is clear that Oracle stored procedures will not support the return of even a single result set via JDBC or ODBC. I am not prepared to select every attribute to a variable, row by row, aggregate them into arrays and return them through OUT arguments.

Oracle's seemingly "brain dead" stored procedure design is of no help here.

This means that for a complex business object, requiring 3 selects to populate, I will need to create and execute 3 separate queries. This means I will incur the overhead of interpreting each query as if the server had never seen it before. I will also incur the connection overhead 3 times instead of only once.

I will move this thread to comp.lang.java.databases.

Thanks
Gary

SKMGMT wrote in article <19970917021801.WAA21237_at_ladder01.news.aol.com>...

>Hi:
>
>In a PL/SQL block in oracle you have to select values into variables. In
>your case you should declare variables for the fields with datatypes
>matching with the datatypes of selected fields.
>Then with the help of an into clause select the valuse of the fields into
>the corresponding variables.
>eg.
>Create or replace procedure pro1 (Arg1, Arg2)
>as
> Var1 %type
> Var2 %type
> Var3 %type
> Var4 %type
>Begin
>Select field1, field2, field3, field4 into var1, var2, var3, var4
>from table_name.
>
>Rajesh
Received on Wed Sep 17 1997 - 00:00:00 CDT