Re: JDBC and stored procedures

From: Pete Haidinyak <haidinyk_at_dt.wdc.com>
Date: 1998/04/14
Message-ID: <3533A179.CAD93859@dt.wdc.com>#1/1

What I do is in the stored procedure setup a return type such as

TYPE <Type Name> is REF CURSOR RETURN <table%ROWTYPE>

and in the stored procedure I

FUNCTION <name>

    RETURN <Type Name> IS
<variable name> <Type Name>
BEGIN
    OPEN <variable name> FOR <select statement>     RETURN <variable name>
END <name>

and on the Java Side my code looks like

import oracle.jdbc.driver.*; // This is needed for the Oracle Object Type.

    String SQLString = "{call ? := <procedure name>}";     CallableStatement stmt = null;

    try
    {

      stmt = <Current SQL Connection Name> ().prepareCall (SQLString);
      stmt.registerOutParameter (1, OracleTypes.CURSOR);         //  --
Tell it is an Oracle Cursor Object
      stmt.execute();
      ResultSet rset = (ResultSet)stmt.getObject (1);     //  --  Retreive
the Cursor.
      rset.next();

     <get the data with rset.get<whatever> statements>

    }
    catch (Exception e)
    {
      System.out.println ("There be and error message : " + e.getMessage ());

    }

This seems to work for me, might be a better way to do it, I have not looked into it.

    Pete

Bruno Robin wrote:

> Hello,
>
> Is it possible to get a ResultSet from a call to a stored procedure ?
> What is the syntax (in PL/SQL) for a stored procedure to return a table
> ? Any example is welcome.
>
> Thank you for your help.
>
> Bruno Robin
> Alcatel
Received on Tue Apr 14 1998 - 00:00:00 CDT