Re: Oracle Stored Procedure returns ResultSet

From: Aaron Freeman <alfreema_at_southwind.net>
Date: Sun, 02 Aug 1998 23:14:53 -0500
Message-ID: <35C5393C.34329B89@bigfoot.com>

Here is a quick and dirty sample. This may not be the most optimal way (I would investigate using stored functions to return the REF CURSOR). This procedure does work; but, you'll need to set up a view called sample_view that has four columns with the same datatypes referenced in the SELECT statement of the getColumns stored procedure.

Aaron Freeman,
Oracle/Project Consultant
Adroit Corporation
Wichita, KS
alfreema_at_bigfoot.com

CREATE OR REPLACE PACKAGE pSamplePackage IS

    TYPE SampleCursorType IS REF CURSOR RETURN sample_view%ROWTYPE;     PROCEDURE getColumns(

        n_key       IN VARCHAR2,
        out_cursor  IN OUT SampleCursorType

    );
    PROCEDURE getColumns(

        n_key       IN VARCHAR2,
        out_cursor  IN OUT SampleCursorType

    ) IS
    BEGIN

        OPEN out_cursor FOR
            SELECT t1.col1, t1.col2, t2.col1, t2.col2
              FROM table1 t1, table2 t2
             WHERE t1.col1 = n_key
               AND t1.col1 = t2.col1;

    END;
END; pax_prakarsa_at_my-dejanews.com wrote:

> I need to write an Oracle stored procedure that executes a query and returns a
> java.sql.ResultSet object. Could anyone give me an example of such a stored
> procedure.
>
> The java code that I am going to use to get the java.sql.ResultSet object is
> the following:
>
> Note: callstmt is a CallableStatement which should be defined according to the
> signature/parameter definition of the stored procedure.
>
> // EXECUTE CALLABLE STATEMENT SQL
> boolean ret = false;
> ResultSet results = null;
> int updateCount = 0;
> try {
> ret = callstmt.execute();
> if (ret == true){
> results = callstmt.getResultSet();
> }
> else{
> updateCount = callstmt.getUpdateCount();
> }
> return ret;
> } catch(Exception e){
> System.out.println(e);
> }
>
> Thanks
> Pax
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Sun Aug 02 1998 - 23:14:53 CDT