Re: Returning more than a sigle variable from a stored function and recive the information in a JDBC Servlet

Mattias Malmgren wrote:

I now found out myself. Becouse it is a importiont question I anerwe it myself so that others can find out as well.

First make a stored function that returns a ref cursor. To return a a cursor it seams that one
have to make a Type that is a ref cursor in a package first:

create or replace package types
 as

     type cursorType is ref cursor ;
end;

CREATE OR REPLACE FUNCTION telprod3 RETURN types.cursortype IS
  l_cursor types.cursorType;
BEGIN
  OPEN l_cursor FOR SELECT * FROM telbok; RETURN l_cursor;
end;

Then the JAVA-code looks like this:

           import oracle.jdbc.driver.*;
            CallableStatement cstmt = con.prepareCall( "{? = call
telprod3()}" ) ;
            cstmt.registerOutParameter (1, OracleTypes.CURSOR);
            cstmt.executeQuery();
            ResultSet cursor = ((OracleCallableStatement)cstmt).getCursor
(1);
            out.println("<p><table border>");
            while (cursor.next ()){
               out.println ("<tr><td>"+cursor.getString
(1)+"</td><td>"+cursor.getString(2)+"</td></tr>");
            }
            out.println("</table><p>");

 / Mattias

> I need help on writing a stored funtion i Oracle and then use it in a
> JDBC servlet.
>
> How do I get a servlet to get multipel rows and cols from a stored
> function?
>
Received on Wed Jun 24 1998 - 09:38:58 CDT