Returning a result set from a stored procedure 
Author Message
 Returning a result set from a stored procedure
Oracle novice here, so please excuse the question.

Is there a way to return a result set from a stored procedure?  That is,
if I wanted a stored procedure to return a result set, and then be able
to have access to that result set via JDBC (just like a select
statement), what can I do?

Thanks,

Tom



Tue, 29 Jul 2003 04:53:52 GMT
 Returning a result set from a stored procedure

As this question has  been asked and answered a zillion of times either read
up in the PL/SQL documentation on REF CURSOR or search the newsgroup
archives at www.deja.com for archived answers.
If you suspect your question is a FAQ, please visit the archives *before*
posting.

Regards,

Sybrand Bakker, Oracle DBA


Quote:
> Oracle novice here, so please excuse the question.

> Is there a way to return a result set from a stored procedure?  That is,
> if I wanted a stored procedure to return a result set, and then be able
> to have access to that result set via JDBC (just like a select
> statement), what can I do?

> Thanks,

> Tom



Tue, 29 Jul 2003 05:26:38 GMT
 Returning a result set from a stored procedure


Quote:
> Oracle novice here, so please excuse the question.

> Is there a way to return a result set from a stored procedure?  That
is,
> if I wanted a stored procedure to return a result set, and then be
able
> to have access to that result set via JDBC (just like a select
> statement), what can I do?

> Thanks,

> Tom

No. No and No. Oracle does not generate a result set. Ever.

If you read the JDBC Developer's Guide and Reference Release 2.0,
Chapter 11 speaks to the process of generating within Java a result
set. Basically you need to develop the appropriate code. If done
properly, you can have a scollable, updatable result set.

Sorry for this short tirade. However, if you are a novice, you need to
actually read alot. Oracle has many features and some limitations. You
should be aware of them long before you start writing code. Knowing
that Oracle does not generate a result set is practically the first day
of school. Get some good books and start reading.

--
Michael Krolewski
Rosetta Inpharmatics

              Usual disclaimers

Sent via Deja.com
http://www.deja.com/



Tue, 29 Jul 2003 08:56:26 GMT
 Returning a result set from a stored procedure


Quote:


> > Oracle novice here, so please excuse the question.

> > Is there a way to return a result set from a stored procedure?  That
> is,
> > if I wanted a stored procedure to return a result set, and then be
> able
> > to have access to that result set via JDBC (just like a select
> > statement), what can I do?

> > Thanks,

> > Tom

> No. No and No. Oracle does not generate a result set. Ever.

> If you read the JDBC Developer's Guide and Reference Release 2.0,
> Chapter 11 speaks to the process of generating within Java a result
> set. Basically you need to develop the appropriate code. If done
> properly, you can have a scollable, updatable result set.

> Sorry for this short tirade. However, if you are a novice, you need to
> actually read alot. Oracle has many features and some limitations. You
> should be aware of them long before you start writing code. Knowing
> that Oracle does not generate a result set is practically the first day
> of school. Get some good books and start reading.

> --
> Michael Krolewski
> Rosetta Inpharmatics

>               Usual disclaimers

> Sent via Deja.com
> http://www.deja.com/

Knowing that Oracle *is* capable of creating a resultset is first day
knowledge. Please stop distributing this misleading information.

Regards,

Sybrand Bakker, Oracle DBA



Tue, 29 Jul 2003 16:04:39 GMT
 Returning a result set from a stored procedure
Hi Tom,

to answer you question, unzip a demo which comes with Oracle (8i):

ora81\jdbc\demo\demo.zip
and then checkout
oci8\basic-samples\RefCursorExample.java

It shows everything: How the procedure(function) must be built and what you
have to do with JDBC to get the resultset.

Matz

P.S. Hmm dunno if it's bad style but here's the File so you don't need to
search for it:
(anyway it's worth to look at the demos)

RefCursorExample.java

/*
 * This sample shows how to call a PL/SQL function that opens
 * a cursor and get the cursor back as a Java ResultSet.
 */

import java.sql.*;
import java.io.*;

// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;

class RefCursorExample
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());


    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }

    // Connect to the database
    Connection conn =
      DriverManager.getConnection (url, "scott", "tiger");

    // Create the stored procedure
    init (conn);

    // Prepare a PL/SQL call
    CallableStatement call =
      conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");

    // Find out all the SALES person
    call.registerOutParameter (1, OracleTypes.CURSOR);
    call.setString (2, "SALESMAN");
    call.execute ();
    ResultSet rset = (ResultSet)call.getObject (1);

    // Dump the cursor
    while (rset.next ())
      System.out.println (rset.getString ("ENAME"));

    // Close all the resources
    rset.close();
    call.close();
    conn.close();

  }

  // Utility function to create the stored procedure
  static void init (Connection conn)
       throws SQLException
  {
    Statement stmt = conn.createStatement ();

    stmt.execute ("create or replace package java_refcursor as " +
    "  type myrctype is ref cursor return EMP%ROWTYPE; " +
    "  function job_listing (j varchar2) return myrctype; " +
    "end java_refcursor;");

    stmt.execute ("create or replace package body java_refcursor as " +
    "  function job_listing (j varchar2) return myrctype is " +
    "    rc myrctype; " +
    "  begin " +
    "    open rc for select * from emp where job = j; " +
    "    return rc; " +
    "  end; " +
    "end java_refcursor;");
    stmt.close();
  }

Quote:
}



Tue, 29 Jul 2003 17:32:18 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. return multiple result sets from a stored procedure

2. Returning a result set from a Stored Procedure

3. How not to return multiple result sets from a stored procedure

4. Returning Multiple Result Sets for Java Stored Procedures

5. returning result sets from stored procedures

6. Returning result sets in oracle stored procedure (OLEDB)

7. Can't update result set returned from stored procedure using RDO

8. Returning result set from function to stored procedure is very slooooooooow

9. best way to return a paged result set from a stored procedure

10. returning result sets from stored procedures

11. Stored Procedure/Multiple Return Results/Set Statement

12. return result set from stored procedure


 
Powered by phpBB® Forum Software